Power Query to Sum a Column and Divide by the Sum of Anothr Column

[legalhustler]

In Power Query I want to sum one column (C) then divide it by the sum of another column (E), therefore the result column will have the same amount repeated on each row. I don't know want to use Group BY because I want to show all the remaining columns as well as the amounts in Column C and E. How can I achieve this?

Result column calculation: (Sum of Column C) 50/10 (Sum of Column E) = 5

Example

Column C	Column E	Result
10	5	5
10	2	5
30	3	5

 

[GraH]

Though group by is a possibility...

Standard UI solution

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column C", Int64.Type}, {"Column E", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {}, {{"Details", each _, type table [Column C=nullable number, Column E=nullable number]}, {"SumC", each List.Sum([Column C]), type nullable number}, {"SumE", each List.Sum([Column E]), type nullable number}}),
    #"Inserted Division" = Table.AddColumn(#"Grouped Rows", "Division", each [SumC] / [SumE], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Division",{"SumC", "SumE"}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Removed Columns", "Details", {"Column C", "Column E"}, {"Column C", "Column E"})
in
    #"Expanded Details"

 

[legalhustler]

That's it! But I can't get your code to work. Error shows "Token Eof expected" and when I click show error it points to the second line where it says #"Changed Type" . I created a Excel Table with the two columns (C & E) and named it "data" then selected Blank Query and pasted your code but to no avail. Please advise.

 

[legalhustler]

NVM - I was able to get it to work and see what you did. Thanks!

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column C", Int64.Type}, {"Column E", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {}, {{"Details", each _, type table [Column C=nullable number, Column E=nullable number]}, {"SumC", each List.Sum([Column C]), type nullable number}, {"SumE", each List.Sum([Column E]), type nullable number}}),
    #"Inserted Division" = Table.AddColumn(#"Grouped Rows", "Division", each [SumC] / [SumE], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Division",{"SumC", "SumE"}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Removed Columns", "Details", {"Column C", "Column E"}, {"Column C", "Column E"})
in
    #"Expanded Details"

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.AddColumn(Source,"Result",each List.Sum(Source[Column C])/List.Sum(Source[Column E]))
in
    Custom1

 

[legalhustler]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.AddColumn(Source,"Result",each List.Sum(Source[Column C])/List.Sum(Source[Column E]))
in
    Custom1

View attachment 41941

This is great too - less steps. Thanks!

 

[shaowu459]

This is great too - less steps. Thanks!

You are welcome