# Power Query: Aggregate very slow



## Craine22 (Jan 5, 2015)

Hello,

im stuck with power querys aggregation tool.

In a table like this:

A | B
1 | 10
1 | 20
1 | 20
1 | 30
2 | 10
2 | 20
2 | 30
3 | 10

i want to use the easy aggregation tool for getting the average from Colum B for all similar numbers in Colum A:
= Table.AggregateTableColumn(Zusammenführen, "NewColumn", {{"10", List.Average, "Anzahl von NewColumn.10"}})

Result should look like this

A | B  | C
1 | 10 | 20
1 | 20 | 20
1 | 20 | 20
1 | 30 | 20
2 | 10 | 20
2 | 20 | 20
2 | 30 | 20
3 | 10 | 10

That works realy good! But on my file i have about 300.000 lines which leeds to a never ending loading process.

Does anyone know how i can do this faster?
In Excel theres a formular =Averageifs() which works much more faster. But i cant reproduce it in PowerQuery.

Any help is appreciated


----------



## Kazlik (Jan 5, 2015)

Does it need to be a calculated column vs a measure? If calculated column then you should be able to do something like this.


=CALCULATE(AVERAGE(Sheet1*), FILTER(Sheet1, Sheet1[A] = EARLIER(Sheet1[A])))*


----------



## scottsen (Jan 6, 2015)

I'm too lame at PQ to answer (though, we can always go beg Ken Puls or Chris Webb to help us).  But... if this data is heading into Power Pivot... I would just write a measure in PP.


----------



## Craine22 (Jan 7, 2015)

Thank you for your replies 

unfortunatly I cant reporduce that "CALCULATE" formula. 
In Power Query when i insert a new column to my file and put in your formular (+ changing the Sheet names) 
it just replies that CALCULATE is no known command.

Im just working with Power Query, so no Power Pivot involved.

I think the solution should group colum A numbers first, before going through aggregation and average calculation.
That should reduce the loading time a lot.

Of course the normal Excel formula works best.
=Averageifs(B:B,A:A,A1) 

But at this point my PowerQuery knowledge fails.
And i want it to be an automatic process without manual inserted Excel formulas.

So maybe a Pro like Ken Puls can help here? :D


----------



## Craine22 (Jan 7, 2015)

I found a way to solve it!! 

With a little trick i can combine two functions of PQ:

= Table.Group(#"Delete Columns", {"A"}, {{"Average", each List.Average(*), type text}})

The result then looks like that:

A | B | C
1 | 10 | 20
2 | 10 | 20
3 | 10 | 10

So it calculates every average for the grouped Column A

In the next step i can add this query as a new sheet and combine it with my first sheet to get the result:

A | B | C
1 | 10 | 20
1 | 20 | 20
1 | 20 | 20
1 | 30 | 20
2 | 10 | 20
2 | 20 | 20
2 | 30 | 20
3 | 10 | 10

So i have one last question:

Do someone know how i can combine this group with a new column, so that i dont need an extra sheet?

A new colum:
= Table.AddColumn(#"Last Step", "Average", each 0)
combined with the group function:
= Table.Group(#"Last Step", {"A"}, {{"Average", each List.Average(), type text}})*


----------



## billszysz (Jan 11, 2015)

If I understood correctly....

let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    GrupRows = Table.Group(Source, {"A"}, {{"B", each _, type table}, {"C", each List.Average(*), type number}}),
    ExpandB = Table.ExpandTableColumn(GrupRows, "B", {"B"}, {"B.B"}),
    NewColName = Table.RenameColumns(ExpandB,{{"B.B", "B"}})
in
    NewColName

Link below is to my google drive (file with this solution)
https://drive.google.com/file/d/0B6UlMk8OzUrxb3NBSzdsWkVNb28/view?usp=sharing

Greetings*


----------



## billszysz (Jan 11, 2015)

Or... little different technique

let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    GrupRows = Table.Group(Source, {"A"}, {{"C", each List.Average(*), type number}}),
    MergeTbl = Table.NestedJoin(Source,{"A"},GrupRows,{"A"},"New Column"),
    ExpandTbl = Table.ExpandTableColumn(MergeTbl, "New Column", {"C"}, {"C"})
in
    ExpandTbl*


----------

