M language in Query Editor

[Anonymous]

Hello,

I am attempting to create a new column that consists of the summed value of the data in column A in Query Editor.  I have tried to use SUM, SUMX, LIST.SUM and each time, I keep getting an error message that the name "____ " was not recognized. Make sure it's spelled correctly.

Not sure what I am doing wrong, my formula looks like below:

=sum([Contract Total])

I have tried lower case as well as upper case and both return the same error message.  

I understand that I can create a calculated column and use DAX instead in modeling, but I want the column to be in the query editor because I want to do more with the created column once it is created.  I have searched for answers, but all the answers seem to be just use DAX and create a calculated column. 

Can't this be achieved in Query Editor by using an M formula??

Thanks for the help!

[MarcelBeug]

Sure, but DAX functions are not recognized in the Query Editor. DAX and M are 2 completely different languages.

Step 2 in this code:

let
    Source = #table(type table[Column A = number],List.Zip({{1..10}})),
    #"Added Custom" = Table.AddColumn(Source, "Sum", each List.Sum(Source[Column A]))
in
    #"Added Custom"

resulted from these actions in the Query Editor:

[Anonymous]

Thank you for replying, I really loved the video and the music!

I tried using that formula, and I received a new error message - The column 'Column1' of the table wasn't found.

Here is the final SQL:

let
Source = Excel.Workbook(File.Contents("\\scf\home\trossgreene\Desktop\Test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sum", each List.Sum(Source[Column1])),
Sum = #"Added Custom"{0}[Sum]
in
Sum

I tried it first on my existing table and I received the same error message, so I created a simple Excel document with rows of numbers to see if I could get it to work and both times I received the same error message.  

The data in my original pbix has been altered quite a lot so I am not starting with a clean pure "Source" like what you appear to have in your video example.  But even when I tried to have something pure like my Excel spreadsheet; Power BI still added a few steps, and there really is no way for me to work with pure unchanged data to begin with, I have to alter the data in some way to fit my needs.

Not sure why I am getting the current error message. 

Step 1Step 2ResultsResults 

[MarcelBeug · Accepted solution]

You should refer to the previous step, in your case #"Changed Type".

Otherwise if you only want the sum, then you don't need to add a column and drill down to the first element in the column.
Instead, you can get the sum directly.

let
    Source = #table(1,List.Zip({{1..10}})),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Calculated Sum" = List.Sum(#"Changed Type"[Column1])
in
    #"Calculated Sum"

[Anonymous]

Thank you!

That worked perfectly. After our first exchange, I tried many different methods to duplicate your results, but none of them worked.

By the way, I love the music on your video!

Thanks again.