Power Query group-by performance

[enricofa]

Hi guys,

I have a performance issue with a query that get stuck on the group-by step, and I would like to understand if there's something I can do to improve the query.

Basically, I have two tables Parameters1 and Parameters2, containing info from different sources. I have to combine them in a single table FT_Parameters to store unique parameters with an additional column to know the source (1, 2 or both), like in the example below.

I have this

Parameters1		Parameters2
Param		Param	Type
A		B	Input
B		C	Output
C		D	Input
E

And I want to achieve this

FT_Parameters
Param	Type	Source
A	-	1
B	Input	Both
C	Output	Both
D	Input	2
E	-	1

Currently I append the two tables and then I use a group-by taking the max of type and a new column with row count. Finally I create a new conditional column and remove count column

Appended = Table.Combine({Parameters1, Parameters2})
Grouped = Table.Group(Appended, {"Param"}, {{"Param_Type", each List.Max([Type]), type text}, {"Count", each Table.RowCount(_), Int64.Type}})
AddedSource = Table.AddColumn(Grouped, "Source", each if [Param_Type] = null
                                                     then "1"
                                                     else if [Count] = 1
                                                     then "2"
                                                     else "Both")
RemovedCount= Table.RemoveColumns(AddedSource, {"Count"})

This solution is very slow even with a little data. Is there some trick to improve performance (or alternative solutions)?

I've tried using join between tables but as I expected it was even slower.

Thank you,

Enrico

[ImkeF · Accepted solution]

Hi @enricofa ,

these transformations shouldn't be terribly slow per se.
So I'd recommend to buffer the data that goes into them:

Appended = Table.Buffer(Table.Combine({Parameters1, Parameters2}))
Grouped = Table.Group(Appended, {"Param"}, {{"Param_Type", each List.Max([Type]), type text}, {"Count", each Table.RowCount(_), Int64.Type}})
AddedSource = Table.AddColumn(Grouped, "Source", each if [Param_Type] = null
                                                     then "1"
                                                     else if [Count] = 1
                                                     then "2"
                                                     else "Both")
RemovedCount= Table.RemoveColumns(AddedSource, {"Count"})

[enricofa]

Thank you @ImkeF,

It results in a 10% faster refresh. Unfortunately it's still too slow for my purposes.

I find it strange too, maybe it is due to the key I use for grouping (column Param) being a long text field.