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 Ra...
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))
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?

       

      • Stachu's avatar
        Stachu
        Community Champion
        8 years ago

        #"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