Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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??
Solved! Go to Solution.
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
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.
Check out this answer from imkeF
Hi @serena11 ,
You might look at the post above from imkeF.
Nathaniel
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
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.