0

When I create a calculated field in Microsoft Power Query feature ("Get and Transform" since Excel 2016) I use a combination two simple formulae: Value.Divide() and Value.Add(). For some reason, this does not work and all I get is null values.

Here is the series of steps that I do:

  1. Get&Transform data from .csv;
  2. Arrange fields (columns), rename;
  3. Add some custom ones;

The one giving me problems is as follows:


= Table.AddColumn(#"Add Cost per result", "ROAS", each Value.Divide(Value.Add([Website conversion value], [#"Mobile app purchases conversion value (corr.)"]), [#"Amount spent (GBP)"]))

Or, as copied from a "graphical interface":

= Value.Divide(Value.Add([Website conversion value], [#"Mobile app purchases conversion value (corr.)"]), [#"Amount spent (GBP)"])

  • [Website conversion value] is imported from the original .csv;
  • [#"Mobile app purchases conversion value (corr.)"] is calculated field ("Add column...");
  • [#"Amount spent (GBP)"] is imported from the original .csv, I suspect that this might be the problem one - why does it have a # prepended when it was there originally during the import? Isn't this a symbol for a "table"?;

Many thanks! Alex

1 Answer 1

0

I solved it. It seems that the "Value. ...()" formulae in Power Query do not work with null values. So, I replaced the null records with a 0, which might not be the most correct approach but it worked.

Problem solved :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.