cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Incremental refresh and Group By in M/PowerQuery

Hey all,

 

just started to try out the incremental refresh, but I have a question:

 

Imagine I have a data source that contains all transactions per store per day. I set up the datetime filter that incremental refresh requires. After that I use group by in powerquery to display the data only per store, leaving out the date column.

 

I then publish this report to service, and set a refresh schedule.

 

My question is: what happens in the service? Does incremental refresh still make sense in this instance? It seems to me that because you did a group by, and powerBI only stores the end result of the query, and not the intermediate steps, it would still need to do a full refresh of all your data every time, and the incremental refresh would not work at all.

 

Is that assumption correct?

 

Thanks!

Jaap

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

Why do you want to remove these date fields?
AFAIK, 'incremental refresh' is based on date query parameters and corresponding date fields. If these date fields removed, incremental refresh may not be triggered.

Incremental refresh in Power BI#filter-large-datasets-in-power-bi-desktop 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

So I want to remove the date fields because I want to aggregate users by Country. If I leave in the date field, it will do unique userID counts per day, but that means a returning user would be counted twice in the final table. By removing the date field and aggregating to Country, the unique counts are correct.

 

I could also just load the entire source table, but depending on the client, data (not in this table, but in others) does run into the billions of rows territory.

 

And yes, so I understand that incremental refresh is based on a date field. I just think that if the user removes the date field in powerquery, maybe the incremental refresh switch (when you right click on a table) should be greyed out instead of active. It also still mentions the date column that I have removed, which seems to indicate that this would still work, see screenshot:

 

Incremental refresh.png

Hi @Anonymous,

Maybe you can try to create a new query to refer from the current query table and remove date field and grouping in new table, then cancel the loading of original query table. After these, the date fields should exist in the original table that not displayed.
Since the official document also not explain this cleanly, you can consider contacting to power bi team to know more about incremental refresh.

Regards,

Xiaxoin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Aw, seemed like a nice workaround, but when I do this, the loaded table doesnt let you set incremental refresh on it, because it doesn't contain the filtering steps on rangestart and end that incremental refresh requires.

 

I think for now I will just assume that Incremental refresh and group by statements that remove the datetime fields do not play nice together. It makes sense.

 

Jaap

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!