cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
anurag_sethi Frequent Visitor
Frequent Visitor

'Group by' refreshes data

I get data from 6 different data sources and put them in 6 tables. I then combine (Table.combine) the 6 tables. They all have a specific column common. When I try to do 'Group by' on that column, the data from the 6 sources refreshes again (with the first refresh at the time of combining the data). This makes the whole refresh very slow for the combined table.
Is is possible to set that it doesn't refresh the data the second time (Group by)? Please help.

4 REPLIES 4
jborro Member
Member

Re: 'Group by' refreshes data

HI @anurag_sethi 

 

Does this relate to what you experience in PQ Editor or this is when you refresh the dataset after it is exported to the Designer?

In PQ Editor this may be a norm as it reads and refreshes underlying datasets when it thinks it needs it.

 

I tested the Table.Combine()  behaviour and it looks like it reads every data source just once, assuming none of the underlying tables is exposed/returned from the Editor. If you return the underlying tables as well as the combined table, it will read the data source twice: the first time for the table and then for the combined table (unless you are doing this in PBI Dataflow). Could you please check with the view of the above?

 

Kind regards,

JB

anurag_sethi Frequent Visitor
Frequent Visitor

Re: 'Group by' refreshes data

Thanks @jborro,

 

It's not the Table.Combine, but Table.Group that reads the data the second time. I am talking about the refresh in the Desktop, not the PQ Editor itself. So once the query has applied changes, I want to refresh the data daily. When I select the combined table and try to refresh, it refreshes all the 6 tables twice, once at Table.Combine and then at Table.Group step.

jborro Member
Member

Re: 'Group by' refreshes data

Hi mate,

Sounds a bit strange. I tested grouping too, and it did not produce an additional data import from files, therefore I decided not to update my reply.

Try Table.Buffer () each imported table before using in Table.Combine. But if they are large enough to create a substantial delay when loading it may make the overall execution time longer even if it helps reducing the number of loads.

For my knowledge, how do you trace the execution? As far as I know PBI does not have a debugger and it's virtually impossible to tell what happens at what stage. I will do more testing, so it also would be great to know what is the type of your data source? Excel files?

Thanks
JB
anurag_sethi Frequent Visitor
Frequent Visitor

Re: 'Group by' refreshes data

Thank you @jborro for the help.

The data is a mix of SQL table, excel file and API retrieved. Each of the 6 tables has around 30k around. When I combine and do a Groupby, it gives 50k unique entries.

I didn't try using Table.buffer because the first step in this 'combined' (7th) table is the Table.combine. So the data from the individual tables is not in the context of this combined table.

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,576)