Power Query: Aggregate very slow

[Craine22]

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]

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]

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]

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]

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]

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]

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