Search code examples
excelpowerquerym

How to divide rows in Power Query using groupings and averages?

I'm strugling in PowerQuery and could use som help getting my data ready for Excel.

I have this list:

Column A Column B Column C
A CA 10
A CA 30
A EA 1
A EU 200
B CA 5
B EU 10
C EA 2

Where i want to divide "LETTER"(EU) with the average of all "LETTER"(CA) and end up with this

Column A NEW COLUMN
A 200/((10+30)/2) --> 10
B (10/5) --> 2

I have tried to group column A, but i'm getting nowhere.

What to do?

Solution

  • Here you go.

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column B] <> "EA")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column A"}, {{"New Column", (x)=>
        let 
            EU =  List.Max(x[Column C]),
            CACount = List.Count(List.Select( x[Column B], each _ = "CA")),
            CASum = List.Sum( Table.SelectRows(x, each _[Column B] = "CA")[Column C])
        in EU/(CASum/CACount), 

    type nullable text}})
in
    #"Grouped Rows"