Forum Discussion

serena11's avatar
serena11
Regular Visitor
6 years ago
Solved

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??

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

7 Replies

    • serena11's avatar
      serena11
      Regular Visitor
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

     

    • serena11's avatar
      serena11
      Regular Visitor
      6 years ago

      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.