Forum Discussion
M language in Query Editor
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!
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"
4 Replies
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:
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
SumI 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
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"