Incremental refresh with ID (YYYYMMDD)

[Anonymous]

Hi all

I wanted to try incremental refresh but got stuck since we use IDs for date related columns in our warehouse. I can't use the ID for the filtering with the two parameters RangeStart and RangeEnd.

In the documentation, a hint is provided that gives you a formula to convert from a Date/Time Value to an ID such as YYYYMMDD and it should be applied in the "filter step":

(x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

However, I can't even get to that filter step (which should let me select the parameters with the custom filter) if I don't even have a Date/Time column in my table. Am I missing something?
How do I set up a table for incremental refresh when I have an IdDay in the format of YYYYMMDD?

Thanks for the help!

[Stachu · Accepted solution]

basically you need to apply filter on IdDay, based on RangeStart & RangeEnd parameters
sample syntax:

=Table.SelectRows(#"Changed Type", each [IdDay] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [IdDay] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))

[Anonymous]

Hi @Stachu

Thanks! Where can I put this formula? Or in other words, where can I define a filter in PowerQuery according to that M formula? Do I need to go into the advanced editor?

Almomst there, thanks again.

Regards

[Anonymous]

@Stachu


I answered it myself, there seems to be no other way than Advanced Editor. So I put in your formula:

  #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [IdDay] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [IdDay] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))
in
    #"Filtered Rows"

But PowerQuery gives me an error saying:

Expression.Error: The name 'Changed Type' wasn't recognized.  Make sure it's spelled correctly.

Something wrong with the formula?

[Stachu]

#"Changed Type" was the name of the previous step in my mock up table
you need to adjust it to whatever is the last step in your query
Also you don't need to go to Advanced editor, it's sufficient to press 'fx' = that will create a new step, and you can paste the formula there

[Anonymous]

Great! Now I got it to work.
Had to make sure the formula bar was actually enabled, otherwise there is no way to enter a formula.

Thanks for the help!

[Anonymous]

Hello

I understand how you use the non date field to put the filters in your Power Query and filter the data as per 2 parameters.

However, once you apply your changes and enable Incremental Refresh for your table, it does expect a datetime field, isn' it?

How did you handle that?

Regards