cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
astrbac Frequent Visitor
Frequent Visitor

Combination Value.Divide() and Value.Add() returns all null results?

Hello,

 

as I came to grasp with M language basics, a strange problem started bugging me. Creating a simple custom column by adding two and dividing them by third.

 

This is what I usually do:

 

1. "Get&Transform" data from a .csv;

2. I sort my columns (fields);

3. I then add a few custom columns, usually very simply like [Column2] / [Column1];

4. I then tried this column which started making trouble:

 

= ([Website conversions value] + [#"Mobile app purchases value (corrected)"]) / [#"Amount spent (GBP)"]

This failed miserably. Returned a whole field of null values.

I triple checked - all three fields that I use in the formula have values.

 

I then learned a bit of M and tried this:

 

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

Also no luck, all null values. Any idea what might be wrong?

 

Thanks all in advance!
Alex

 

3 REPLIES 3
rafaelmpsantos Established Member
Established Member

Re: Combination Value.Divide() and Value.Add() returns all null results?

Before thi check if all the columns is type as number. (in the column head you may see something like 123 or 1.2)

Community Support Team
Community Support Team

Re: Combination Value.Divide() and Value.Add() returns all null results?

Hi astrbac,

 

What's your data type? Could you show some sample data?

 

Regards,

Jimmy Tao

astrbac Frequent Visitor
Frequent Visitor

Re: Combination Value.Divide() and Value.Add() returns all null results?

Hi guys!

 

I don't even know how to share this dataset both with the model, except to share the real WOrkbook. I can't do that because there is client data in it.

 

If you mean "are the fields that I am adding and then dividing of the proper data type" BEFORE I use them in my calculation - the answer is yes.

 

- [Website purchases value] is imported from .csv and properly declared (automatically) as 1.2 (decimal number);

[Mobile purchases value] is imported from .csv and properly declared (automatically) as 1.2 (decimal number);

 

My first calculation of correcting [Mobile purchases value] works.

 

= if List.Contains({"iPhone", "iPad", "iPod"}, [Impression Device]) then ([Mobile app purchases conversion value]/2) else [Mobile app purchases conversion value]

After this step, I change the type of this new column to decimal number.

 

Now I try to use this new column in the second calculation, as in my post above, both of the fields in question have been declared as decimal number, and it simply does not work.