Forum Discussion
Incremental Refresh Power BI
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.
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.
Thank you for your help.
In a table, I have an effective from date and the effective to date and I need to create a list Dates column of these dates where I used M language to calculate the list. Then when I tried to create an incremental refresh there is a warning. Is there a way to create a list of dates between the two dates that wouldn't break the query folding?Did you try it? That warning isn't always accurate, and the incremnetal refresh article by MS states that. It is saying that at that dialog box, it cannot determine if it will fold. I get that warning 9 out of 10 times, and it is wrong every time. I can see what folds better in Power Query by looking at the code and right-clicking on steps subsequent to the filter for Incremental Refresh and see that it is folding.
This is the M code in use to create the list that broken the query folding. and when I ran diagnostic in the query there is a warning about query folding. Do you happen to have a way to create a list of dates without breaking the query folding? M formula use to calculate the listDiagnostic ran for that custom column