cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mpalha
Helper I
Helper I

Incremental Refresh with Text Date Field YYYY-MM-DD

Hello,

I am trying to implement incremental refresh for my large dataset. For the inital load into Power BI Desktop, I am having trouble. My Date column is a text field in the format YYYY-MM-DD. How do I set up the initial load with the RangeStart and RangeEnd parameters?

I have tried changing the format of the column to Date/Time in Power Query and then filtering the the data based on the RangeStart and RangeEnd parameters, but it is still loading beyond the expected number of rows.

Any help is appreciated. Thanks!

1 ACCEPTED SOLUTION

@mpalha 

You hit the right article. If your query is not folded, then the Power Query engine needs to bring all the data to perform the transformations. I am not sure what your data source is.


The best option is to ask the data source owner to convert the date field from text to date type in the source itself.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

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

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

7 REPLIES 7
amitchandak
Super User IV
Super User IV

@mpalha , YYYY-MM-DD  you should able to convert to date. By changing data type

 

You can refer this also how to use Day Key

https://www.fourmoo.com/2020/03/11/how-to-configure-incremental-refreshing-in-power-bi-with-datekey-...

or

https://community.powerbi.com/t5/Desktop/Incremental-refresh-with-ID-YYYYMMDD/td-p/437844



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for the reply @amitchandak . Since the column is text, I'm unable to select a custom filter to filter based on parameters.

mpalha_1-1608777476821.png

 

 

@mpalha , As suggested in my last post, Try to change the data type to text, YYYY-MM-DD should work as a date either via power query data type change or Column tools. 

 

See if that can work



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@mpalha  You need to convert the data type to date. Incremental refresh does NOT work on text columns. If you need the column to be text, please duplicate the column, then convert data type. 

 

Right click on the column to duplicate: 

AllisonKennedy_0-1608778448439.png

 

 

Then transform the data type to Date: 

AllisonKennedy_1-1608778512384.png

 



?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Thanks @AllisonKennedy , I changed this column to Date/Time and was able to load the filtered data to Power BI Desktop. 2 things I noticed:

  • When applying the query changes, all rows in the dataset are loaded. Once done, only the data filtered in the query is available in Desktop. Is Power BI Desktop supposed to be loading all rows in the dataset?
  • When setting up incremental refresh, I get a warning that my M query cannot be folded; query folding is necessary for incremental refresh. According to this article changing a column data type prevents query folding. I needed to change the date column to Date/Time and this ultimately does not allow me to do incremental refresh. 

@mpalha 

For your two things: 

  • No, Power BI desktop should not load all rows. Incremental refresh happens in the Power BI web service (PowerBI.com) and once created you cannot download the report to desktop. Same thing, when creating a report with incremental refresh the desktop version just offers the filtered preview.
  • Re query folding; you will have to convert the data type in the original data source to get the benefit of incremental refresh. Once you do, it will behave the same as what you have acheived now but without that warning.


?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

@mpalha 

You hit the right article. If your query is not folded, then the Power Query engine needs to bring all the data to perform the transformations. I am not sure what your data source is.


The best option is to ask the data source owner to convert the date field from text to date type in the source itself.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

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

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

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.