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



## legalhustler (Jun 30, 2021)

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 CColumn EResult105510253035


----------



## GraH (Jun 30, 2021)

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 (Jun 30, 2021)

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 (Jun 30, 2021)

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 (Jun 30, 2021)

```
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 (Jul 1, 2021)

shaowu459 said:


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


 This is great too - less steps.  Thanks!


----------



## shaowu459 (Jul 1, 2021)

legalhustler said:


> This is great too - less steps.  Thanks!


You are welcome


----------

