Forum Discussion
Power Query group-by performance
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
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"})
2 Replies
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"})
