Incremental Refresh with Text Date Field YYYY-MM-DD

[Anonymous]

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!

[amitchandak]

@Anonymous , 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-date-instead-of-the-default-datetime/

or

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

[Anonymous]

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

[amitchandak]

@Anonymous , 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

[AllisonKennedy]

@Anonymous  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: 

Then transform the data type to Date: 

[Anonymous]

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. 

[AllisonKennedy]

@Anonymous 

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.

[Fowmy · Accepted solution]

@Anonymous 

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