Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
serena11
Regular Visitor

Power Query M, the equivilent of SUM??

I want to create a new column in power query which is - each value in one column divided by the SUM of another column - .

 

I am trying to create a new custom column but can't get the right formula for it. I am doing things like:

'(1st column)/List.SUM(column2)'

 

but it just comes up with an error. What is the right way to go about this??

1 ACCEPTED SOLUTION
Anonymous
Not applicable

If you post the code from the advanced editor, I could probably clarify better. Below is a sample table from Excel PowerQuery. Step Source returns a table to which I want to add a column. I can reference source in a calculated column. I don't think it will appear as a drop down.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CalcCol = Table.AddColumn(Source, "Custom", each [Column1]/List.Sum(Source[Column2]))
in
    CalcCol

Hope this helps,

Mike

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

You need to reference the table name for the sum. Presuming that your tablename is Table1, the expression would be:  [Column1]/List.Sum(Table1[Column2]).

 

Regards,

Mike

 

I have entered the table name into the equation but I still get error once it has loaded? The table name doesn't appear on the drop down though, I have to type it in manually.

Nathaniel_C
Super User
Super User

Check out this answer from imkeF 

Hi @serena11 ,

You might look at the post above from imkeF.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




would you be able to break that answer down n how it relates to my question please? I am not familiar with power query M formula so not able to understand it very well.

Hi @serena11 ,

could you please where the M-code that your query has generated so far? 

Home -> in Query: "Advanced Editor" -> Copy everything and paste into thread

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

If you post the code from the advanced editor, I could probably clarify better. Below is a sample table from Excel PowerQuery. Step Source returns a table to which I want to add a column. I can reference source in a calculated column. I don't think it will appear as a drop down.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CalcCol = Table.AddColumn(Source, "Custom", each [Column1]/List.Sum(Source[Column2]))
in
    CalcCol

Hope this helps,

Mike

 

Hi Mike,

Is there any way to do something similar, but write the answer back into "Column 1" instead of a new column "Custom"?

Thanks in advance.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors