Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Incremental refresh with ID (YYYYMMDD)

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!

 

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    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))

6 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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?