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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

        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