Power BI's BEST KEPT SECRET: Incremental Refresh!

[@bharathsp5542]

While creating parameters in power query editor u have given range start and range end as 1year and in power bi desktop in incremental refresh u took historical as 4 years, my question is in historical can we take more years than parameters specified years??

[@KSRDatavizon]

Yes, you can specify more years in the Incremental Refresh historical range than the years set by the RangeStart and RangeEnd parameters in Power Query. The parameters limit data during development, while the historical range controls how much data is loaded and retained in Power BI.

[@Samuel-br6lr]

You are a great Teacher, thank you very much. I am very grateful

[@KSRDatavizon]

You are welcome!, Please subscribe our channel for regular updates.

[@poluankireddy5904]

Good content, it is most useful

[@KSRDatavizon]

Glad to hear that

[@pavanyuva011]

one doubt- u created range start/end date for only one year.. so pbix file will have only 1 year of data. but in the incremental step, for archive u selected 4 years of data. how can u get 4 years of data .bcoz u loaded only 1 year of data right. pls correct me if am wrong

[@vishnupriya2841]

Same doubt for me too

[@KSRDatavizon]

Great question! Let me clarify this: When you're developing a report in Power BI and you create the RangeStart and RangeEnd parameters, you're essentially limiting the data that is loaded into Power Query during development. This is done to improve performance by working with a smaller, manageable subset of your data (like 1 year in this case). However, this limitation is only temporary for the development phase. Once you publish the report to the Power BI Service and enable Incremental Refresh, Power BI uses the incremental refresh policy settings you’ve configured (in this case, keeping 4 years of archived data and refreshing only the recent data). Power BI will automatically load the additional years of data from your data source, even though you only worked with 1 year in Power Query during development. Here's how it works: RangeStart/RangeEnd (Development Phase): These parameters restrict data locally in Power BI Desktop for testing. Incremental Refresh (Service Phase): After publishing, Power BI Service fetches the full data (4 years in your case) based on your refresh settings. So, the 4 years of data will be pulled during refresh, even though your Power BI Desktop (PBIX file) was only working with 1 year of data during development. You're not wrong, but the Incremental Refresh mechanism ensures that all the data (4 years) is loaded during refresh when published to the Power BI Service. Let me know if this clears up the confusion!

[@bharathsp5542]

Wt ever the questions some people are asking please try to answer that questions , simply making videos and uploading that, that doesn't make sense if u answer the questions means people will see more videos of your channel and that it will be good for everyone thanks.

[@KSRDatavizon]

Thank you for sharing this valuable feedback! I’ll make sure to focus on answering specific questions in future videos to provide more value to the audience. This approach will definitely help in building better engagement.

[@OfficialRK498]

Hello Mam can you please explain about why we need to create range start and range end parameters what is used of that and can we choose any random date for that parameter as we set set all parameters in incrimetal in incresmental refresh ther we set archive year as well last days to refresh but i I'm confused as we created these things in incremental refresh then why we need to set range start and range end parameter and is there is relation about this parameter or we can take any date for creating this range start and range end parameter

[@KSRDatavizon]

Hello! Thank you for your question. Let me explain: The RangeStart and RangeEnd parameters in Power Query are used for filtering the data during the development phase. These parameters are crucial when you're setting up Incremental Refresh because they allow you to define a small sample of data to work with while building and testing your report. By limiting the data, you improve performance and speed during development. When you publish the report and enable Incremental Refresh, Power BI Service will automatically handle the full data refresh using the settings you defined (like archive period and last X days to refresh). The RangeStart and RangeEnd you created in Power Query don’t need to correspond to specific dates in your full dataset but should cover a sample range that is useful for testing. So, while the Incremental Refresh settings determine how much historical data is retained and how often new data is refreshed, the RangeStart and RangeEnd parameters serve as placeholders during report development. The key thing is that these parameters help set up the filter logic and shouldn't be completely random-use dates that align with your testing needs. Let me know if this clears up the confusion!

[@madhurarora5533]

Hi @KSRDatavizon, I have multiple fact tables in my model and I would like to have different Start / End Range filters on the different tables. is it possible to make such parameters per individual date tables? Is it possible to set different historical and refresh criteria for each table? Also, do i need to implement incremental refresh on all of my tables or is it possible to do just on those tables that have huge volume of data?

[@KSRDatavizon]

if you have different tables you need to set incremental refresh individually on tables

[@BTS7Armylovers]

Excellent mam

[@KSRDatavizon]

Thank you

[@camassasantiago933]

Hi sister, how will I set up if I would like to Achieve data 3 years before refresh (1/1/2019 - 3/31/2022) but Incremental refresh from 2 months before refreshing until future data (4/1/2022 - 5/31/2025). Because I have some data for the future that needs to be refreshed too?

[@archanasahoo9670]

THANK U FOR THE INFORMATION, PLEASE COMMENT DO WE REQURIED POWER BI PREMIUM ACCESS?

[@KSRDatavizon]

Yes you need it. as you can do it in service

[@ManaTelugodey]

Thank you

[@KSRDatavizon]

You're welcome, please subscribe our channel for regular updates, also its motivate us.Thank you

[@venkatapathybalaji]

If it's a monthly data update through Excel. What would be the process on incremental refresh??

[@KSRDatavizon]

excel cannot be used for incremental refresh

[@mohankarthikballina7707]

Good explanation..after incremental refresh set up, I can see 2019 data only not previous years data. How to see the previous years data.

[@KSRDatavizon]

It depends on how much data you archive it.. If u want to see old years data.. Then u need to select last 10 years of data and for refresh u can choose last 1 year.. By this way.. We can handle this

[@prajaktapawar8513]

Thank u its really helpful.. So we only take dataset from sql while using Incre Ref but not excel csv files? Is it correct or please tell

[@KSRDatavizon]

You're welcome! I'm glad you found the response helpful. To clarify your question: Incremental Refresh in Power BI is typically used with data sources that support query folding, such as SQL databases, because query folding allows Power BI to push filters (like RangeStart and RangeEnd) back to the data source and only load the relevant data for each refresh. However, Excel or CSV files do not support query folding. This means that Incremental Refresh doesn't work with Excel or CSV data sources in the same way as it does with databases like SQL. Since Power BI cannot push queries to Excel or CSV files, it has to load the entire file every time, making incremental refresh not applicable in those cases. So, to answer your question: Yes, Incremental Refresh works with data sources like SQL databases, but not with Excel or CSV files. Let me know if you have any more questions!

[@CePe678]

Hi, where did you used parameters(You just created ) and why 2019 jan to 2019 dec? in parameter

[@ashishsutar5698]

That created parameters are filtered on the dataset date column so only that specified range data is being refreshed during refreshes.

[@KSRDatavizon]

The RangeStart and RangeEnd parameters are used in Power Query to filter data (e.g., Jan 2019 - Dec 2019) during development to work with a smaller dataset for faster performance. When you publish and set up Incremental Refresh, Power BI uses these parameters to manage data refresh automatically. The date range (e.g., 2019) is just for testing purposes and does not limit the actual data size when the report is deployed.

[@KSRDatavizon]

Thank You

[@akshaypatil8155]

Do we have to use "Scheduled refresh" only for direct query mode ? and incremental refresh for import mode? which refresh option to use when ??? tommorow is my interview, kindly answer.....

[@KSRDatavizon]

Nothing like that.. It depends.. But if u are using incremental load.. It will be some database.. U cannot do incremental load for excel or csv data

[@muhammadajmi4887]

Hi.i have a question regarding to incremental refresh..i have like 94 data source which i combine using append to new masterdata..which one should i do the incremental refresh all 94 data or the masterdata?

[@KSRDatavizon]

There is no need to have refresh for appended data.. But u need to set refresh for all source.. Also there is some limitations.. Not every data source will support incremental refresh

[@sunilraj321]

Thanks for the amazing content. Can I schedule the incremental refresh based on the modified date column in the SharePoint List? I would like to schedule the refresh to the rows only modified today.

[@KSRDatavizon]

as of now incremental refresh works for selected source only

[@aishwaryathiyagarajan1159]

If possible please post an video on ytd mtd qtd

[@KSRDatavizon]

sure

[@anshulverma3046]

Hello mam, I have a question can you please tell me without date table , how can I use the incremental refresh in a huge data set ? How can I achieved it ?

[@KSRDatavizon]

Incremental Refresh without a Date Table Natural Key: Use a unique identifier. Timestamp or Version: Compare timestamps or versions. SQL Incremental Loading: Use SQL queries to filter for new or changed rows. Custom Logic: Create custom DAX calculations. Considerations: Performance Data Quality Maintainability

[@nguyenngothuong]

i like your voice 🤩

[@KSRDatavizon]

Thanks

[@AftabALAM-dd6bz]

Hi when you have filter dates for incremental refresh, only that range data available for visualization. what if user want to see all the data.

[@KSRDatavizon]

all data will be available.. only selected dates will be refreshed

[@rajashekar4171]

Please make one video about differnce between Report Refresh and Dataset Refresh, it is my kindly request Thank you.

[@KSRDatavizon]

Noted, will try to make asap

[@rajashekar4171]

@@KSRDatavizon Please upload video

[@javedhassansheik903]

I did the same process as you shown but i am not able to do incremental refresh madam?

[@KSRDatavizon]

Do check the process once again

[@DineshKumar-cu3bg]

How Can we do daily incremental refresh?

[@KSRDatavizon]

You need to have Service to do all these

[@glamourworld8170]

The video was nice.. But pz provide SQL script to practice

[@KSRDatavizon]

sure we will upload

[@edaraboinabhanuprasadmudir8274]

Hi Madam, I want to refresh my data every hour . Madam is it possible? If possible please tell us how to do this

[@MaheshTiwari-hq7lr]

you can use scheduled refresh for that .

[@KSRDatavizon]

Yes, it's possible to refresh your data every hour in Power BI, but this feature is available only if you're using Power BI Premium or Power BI Premium Per User (PPU) licenses. Here's how you can set up hourly refreshes: Steps to set up hourly refresh: Ensure you have a Premium or Premium Per User (PPU) license: Only Premium and PPU licenses allow you to refresh more than 8 times a day. With these licenses, you can schedule refreshes up to 48 times a day (every 30 minutes). Go to the Power BI Service: Publish your report to the Power BI Service (app.powerbi.com). Navigate to the workspace where you published the report. Configure the dataset refresh: Go to the Settings of your dataset. Under the "Scheduled refresh" tab, enable "Keep your data up to date". Add multiple refresh times with intervals (e.g., set times for every hour). Make sure your data source supports automatic refresh: Ensure that your data source supports refresh in Power BI Service. For example, sources like SQL databases or cloud-based data sources (Azure SQL, SharePoint, etc.) typically support scheduled refreshes. For DirectQuery or Live Connection: If your report is built using DirectQuery or Live Connection to data sources, your data will refresh automatically without needing scheduled refreshes, as the data is queried directly from the source on demand. Important Notes: If you are on the Pro license, you're limited to 8 refreshes per day. Consider data source limits, as some sources may have constraints on how often they allow refreshing. Let me know if you need more details or if you’re using a specific data source!

[@ranjith1083]

can you please share any power bi developer resume for 5+ years

[@KSRDatavizon]

its available in our webiste Datavizon.com / student zone

[@ChinthaginjalaPrasad-h4f]

Please ensure accurate information is provided. This query doesn't pertain to incremental refresh. If unfamiliar with a topic, it's advisable to abstain from providing information rather than offering potentially incorrect details to learners.

[@KSRDatavizon]

thank you for your feedback. You're absolutely right, accuracy is crucial. In this particular case, it seems there might have been a misunderstanding about the query's relation to incremental refresh. We're always striving to provide the best possible information, and we appreciate you pointing this out. We're constantly learning and improving at KSR Datavizon. If you have any specific corrections or suggestions, please don't hesitate to share them. It helps us ensure our content remains accurate and valuable for viewers like yourself. As for the topic you mentioned, we'd be happy to create a dedicated video on incremental refresh in Power BI to clarify the process. Would that be helpful for you and other viewers? Thank you for your understanding and for helping us grow!

[@satyabobby6248]

1gb for pro, 100 gb for premium.

[@KSRDatavizon]

correct !!!

[@ChinthaginjalaPrasad-h4f]

Please ensure accurate information is provided. This query doesn't pertain to incremental refresh. If unfamiliar with a topic, it's advisable to abstain from providing information rather than offering potentially incorrect details to learners.

[@KSRDatavizon]

thank you for your feedback. You're absolutely right, accuracy is crucial. In this particular case, it seems there might have been a misunderstanding about the query's relation to incremental refresh. We're always striving to provide the best possible information, and we appreciate you pointing this out. We're constantly learning and improving at KSR Datavizon. If you have any specific corrections or suggestions, please don't hesitate to share them. It helps us ensure our content remains accurate and valuable for viewers like yourself. As for the topic you mentioned, we'd be happy to create a dedicated video on incremental refresh in Power BI to clarify the process. Would that be helpful for you and other viewers? Thank you for your understanding and for helping us grow!

[@ChinthaginjalaPrasad-h4f]

Please ensure accurate information is provided. This query doesn't pertain to incremental refresh. If unfamiliar with a topic, it's advisable to abstain from providing information rather than offering potentially incorrect details to learners.

[@KSRDatavizon]

thank you for your feedback. You're absolutely right, accuracy is crucial. In this particular case, it seems there might have been a misunderstanding about the query's relation to incremental refresh. We're always striving to provide the best possible information, and we appreciate you pointing this out. We're constantly learning and improving at KSR Datavizon. If you have any specific corrections or suggestions, please don't hesitate to share them. It helps us ensure our content remains accurate and valuable for viewers like yourself. As for the topic you mentioned, we'd be happy to create a dedicated video on incremental refresh in Power BI to clarify the process. Would that be helpful for you and other viewers? Thank you for your understanding and for helping us grow!

[@ChinthaginjalaPrasad-h4f]

Please ensure accurate information is provided. This query doesn't pertain to incremental refresh. If unfamiliar with a topic, it's advisable to abstain from providing information rather than offering potentially incorrect details to learners.

[@KSRDatavizon]

Thank you for the clarification, and I appreciate your feedback. I'll make sure to provide accurate information. Regarding your query: The RangeStart and RangeEnd parameters in Power Query are used to define the period for which data is pulled during your query development or testing phase, particularly when dealing with time-based filters. In Power BI, when configuring Incremental Refresh, you can indeed set a historical data range that covers more years than the period specified by the RangeStart and RangeEnd parameters. The parameters in Power Query only limit the data you're working with during development. Once deployed to Power BI Service with incremental refresh enabled, the refresh will consider the broader historical range set in the Incremental Refresh policy, not the initial parameters. So, it’s perfectly valid to set a broader historical data range in the Incremental Refresh settings than the range in Power Query.