Incremental Refresh Power BI

[Nepal101]

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. 

[edhans]

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.

[Nepal101]

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. 

[edhans]

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.

[Nepal101]

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? 

[edhans]

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.

[Nepal101]

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

[edhans]

You can't. A List is a Power Query object, not a SQL Server object, so nothing to send back. But you may still do this if you use it correctly. See this article.

I generate a list. It can come from anywhere. Then I pass that list to List.Contains in SQL Server via a Table.SelectRows() function. It folds using the in operator.

[Nepal101]

I tried what was mentioned in the document but it created an error. I will be needing a List date in order to calculate the measure. Can you please share what you did to create the list of dates using that documentation?
 

[edhans]

Put this in a blank query.

let
    Source = {Number.From(#date(2021,1,1))..Number.From(#date(2021,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type datetime}})
in
    #"Changed Type"

that will give you a list of dates in the datetime format from Jan 1 to Dec 31 of 2021.

But I honestly don't see what a list has to do with a measure. Lists are in Power Query and do not load to the data model. They get stripped off.

I think you are way beyond what is needed for incremental refresh.

I suggest you ask different questions for measures in the DAX forum and just focus on getting incremental refresh to work here.

If you are still having issues with Incremental Refresh, please ask a comprehensive question on your specific issue. All we have are bits and pieces. Remember, we don't have your PBIX file, nor understand your data, so partial questions only delay a good answer. We are about 10 posts deep here already and no solution. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Nepal101]

Thank you for the suggestion. I will be doing that I truly appreciate your reply as I am new to this forum and tool. 

[edhans · Accepted solution]

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.