Forum Discussion
Incremental Refresh Power BI
The table that I am working with contains a date column based on Integer in the YYYYMMDD format. Since I have to convert these numbers into a DateTime, the power query function would break the query folding. Based on the research, the incremental refresh will not work, if the query is not folding properly.
Can you help me convert the Number field to Date/time data type without breaking the query folding?
Thank you for your help.
No prob. Glad to help. If anything I have said helps, please "accept as solution" for that part so this thread can be known to be solved.
11 Replies
No need. Incremental refresh works fine with the YYYYMMDD format. See this article, and the specific section I've linked to. Configure incremental refresh for Power BI datasets - Power BI | Microsoft Docs
You will basically create a custom function:
= (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)The article walks you through each step of it.
Thank you so much for your reply didn't know I could do that too. But I should have explained the scenario in detail. I have a below table with effective from and to date that is in integer and in order to change that into date and time, I had to use an extract function which broke the query folding. I need to change the data type into date/time because I need a list of dates that I created using the list formula. But now when I tried to configure the incremental refresh on the filtering on the list dates it keeps on loading and spinning. I was thinking it might be because the query folding broke when I changed the data type from integer to date/time.
Is there a better way to do this?
and once again thank you for your time much appreciate it.I'm still not clear on what you are asking.
If your source data is integer YYYYMMDD, then just use that. If you need to filter it, then use the same function logic below as your filter. That is, in fact, what incremental refresh is doing. Converting the integer to a true date in the function only, but then when it folds it sends a hard-coded date back.
Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)You cannot convert date to integer or integer to date in the data and preserve folding. You can only do it in a function or variable. Once you are past the Incremental Refresh filter in your query, then you can convert it, but I don't bother. I have a date table with a DateKey in it that is the date in YYYYMMDD format, and I just use that in my filter relationship. It is a bit of extra bagage in the date table, but who cares? Even a 10 year date table is less than 4,000 rows.
If you need more help, please post some specific examples. I cannot see what you are trying to convey in that image.
