Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Incremental Refresh | RangeStart vs RangeEnd Parameters?

Hey all,

 

I'm setting up a incremental refresh for one of my clients. I understand the process of creating the parameters for RangeStart and RangeEnd, then filtering the LastModification date by those two parameters. Then go back into PBI desktop and set the incremental refresh.

 

My question is, what do we put for the RangeStart and RangeEnd dates? Is it supposed to be Start = First date in my dataset - End: Last date in my dataset?


In all YouTube videos, I see a lot of people saying that the range dates are not important becasue it will be overwritten when selecting the incremental refresh... so what do I put for RangeStart and RangeEnd??

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Incremental refresh uses two parameters, named RangeStart and RangeEnd (names are case sensitive). When published, the service will automatically change the parameter values, to load only the data added after the last load, e.g. only for the last day.

Incremental refresh isn't designed to support cases where the filtered date column is updated in the source system, only to load newly added rows. If an update is interpreted as an insertion and a deletion, not an actual update, it might work. If the deletion occurs in the historical range and not the incremental range, it won’t get picked up.

To define the parameters with default values, in the Power Query Editor, select Manage Parameters and add their initial values which will filter the initial load. Once published, the parameter values are overridden automatically by the Power BI service.

vluwangmsft_3-1630399476928.png

 

With the parameters defined, you can then apply the filter by selecting the Custom Filter menu option for a column.

vluwangmsft_4-1630399506110.png

 

Ensure rows are filtered where the column value is after or equal to RangeStart and before RangeEnd. Other filter combinations may result in double counting of rows.

vluwangmsft_5-1630399527186.png

 

Verify queries have an equal to (=) on either RangeStart or RangeEnd, but not both. If the equal to (=) exists on both parameters, a row could satisfy the conditions for two partitions, which could lead to duplicate data in the model, i.e. the same order to be loaded in the model twice in two consecutive loads.

 

Refer:

https://community.powerbi.com/t5/Community-Blog/Incremental-Refresh-with-Subqueries/ba-p/1003337 

https://community.powerbi.com/t5/Desktop/Incremental-Refresh-RangeStart-and-RangeEnd-Values/m-p/510818 

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#refresh-ranges

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Incremental refresh uses two parameters, named RangeStart and RangeEnd (names are case sensitive). When published, the service will automatically change the parameter values, to load only the data added after the last load, e.g. only for the last day.

Incremental refresh isn't designed to support cases where the filtered date column is updated in the source system, only to load newly added rows. If an update is interpreted as an insertion and a deletion, not an actual update, it might work. If the deletion occurs in the historical range and not the incremental range, it won’t get picked up.

To define the parameters with default values, in the Power Query Editor, select Manage Parameters and add their initial values which will filter the initial load. Once published, the parameter values are overridden automatically by the Power BI service.

vluwangmsft_3-1630399476928.png

 

With the parameters defined, you can then apply the filter by selecting the Custom Filter menu option for a column.

vluwangmsft_4-1630399506110.png

 

Ensure rows are filtered where the column value is after or equal to RangeStart and before RangeEnd. Other filter combinations may result in double counting of rows.

vluwangmsft_5-1630399527186.png

 

Verify queries have an equal to (=) on either RangeStart or RangeEnd, but not both. If the equal to (=) exists on both parameters, a row could satisfy the conditions for two partitions, which could lead to duplicate data in the model, i.e. the same order to be loaded in the model twice in two consecutive loads.

 

Refer:

https://community.powerbi.com/t5/Community-Blog/Incremental-Refresh-with-Subqueries/ba-p/1003337 

https://community.powerbi.com/t5/Desktop/Incremental-Refresh-RangeStart-and-RangeEnd-Values/m-p/510818 

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#refresh-ranges

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

parry2k
Super User
Super User

@Anonymous just put any date range, let's say it could be Jan 1, 2021 to Aug 31st, 2021.

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.