Forum Discussion

jusTodd's avatar
jusTodd
Advocate IV
3 years ago
Solved

Simple Subtraction Problem

Attempting to do the death-defying performance of simple subtraction between two columns in Power Query is not giving me the expected results.

 

Still a freshman to whi world, can someone help me understand what is going on here?

 

The story begins after merging two tables to get one field needed from another table to create a new table.

 

I chose "Custom Column," and in the dialog choosing the columns from Available Columns, I wrote [Column A]-[Column B], which provided me with nothing but null values and did no subtraction at all.  Both are Whole Number Columns

 

[#"Total Billable Duration (Min)"]-[ISPDataBasic.Duration Min]

 

I also tried to pull off this daring act of complex arithmatic with the following:

 

"Value.Subtract([#"Total Billable Duration (Min)"],[ISPDataBasic.Duration Min])"

 

It did not work either.  I do not understand.  (edited ... previously said it did work.  It does not.)

 

One thing I did note; it changed what I expected to be simply [Total Billable Duration (Min)] into [#"Total Billable Duration (Min)"].

 

When attempting to correct it to remove the # and the quotes, it indicated there was Invalid Identifier (screen shot below).  Still, that is no help.  What it transformed that portion of the equation into does not work.

 

 

  • jusTodd's avatar
    jusTodd
    3 years ago

    That is exactly what I did!

     

    Thank you for reminding me of the silliest thing.

     

       480-null = null

     

    I needed to replace null with 0 first.

     

    Now it works.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Just CHOOSE your columns from the box on the right. Choose, then write a minus sign, then choose again, and you're done.

     

    --Nate

    • jusTodd's avatar
      jusTodd
      Advocate IV

      That is exactly what I did!

       

      Thank you for reminding me of the silliest thing.

       

         480-null = null

       

      I needed to replace null with 0 first.

       

      Now it works.

  • JamieW135's avatar
    JamieW135
    Frequent Visitor

    Thanks, this really helped me, I forgot that null actually existed in the cell and could be replaced!  So simple.,..