cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
enricofa
Regular Visitor

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 ParamType
A BInput
B COutput
C DInput
E   

And I want to achieve this

FT_Parameters
ParamTypeSource
A-1
BInputBoth
COutputBoth
DInput2
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

 

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

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"})

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User II
Super User II

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"})

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

enricofa
Regular Visitor

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.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors