Forum Discussion

DavidMoss's avatar
DavidMoss
Icon for Advocate V rankAdvocate V
10 years ago

Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

More as a reference for us than a question which hopefully you will find useful :-)   I was just working on filtering data in Power Query based on the date. I kow you can manually filter your sourc...
Tips and Tricks
descalabro's avatar
descalabro
Frequent Visitor
9 years ago

Hello,

 

Thank you for your suggestion. However, I get this error:

 

«Expression.Error: It's not possible to apply the operator < to types Date and DateTime

 

Details:

Operator=&lt;
Left=01-01-2017
Right=30-08-2016 17:00:00»

 

The same message appears whether I write "<" or ">".

 

 

TJ

 

 

Tiago Jordão

  • DavidMoss's avatar
    DavidMoss
    Icon for Advocate V rankAdvocate V
    9 years ago

    If you do it the manual way as my 1st step in the above original explanation can you cut and paste the M code here from query editor > advanced options as it may have something to do with your local date formats ??

    Worth a try anyway...

     

    • descalabro's avatar
      descalabro
      Frequent Visitor
      9 years ago

      Sure.

       

      This is the original M code (works):

       

      let
    Source = Access.Database(File.Contents("D:\Users\tljordao\OneDrive - SONAE\Relatorios Individuais\Carla Costa.mdb"), [CreateNavigationProperties=true]),
    #"_Carla Costa" = Source{[Schema="",Item="Carla Costa"]}[Data],
    #"Added Custom" = Table.AddColumn(#"_Carla Costa", "Nome", each "Carla Costa"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Start", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"EntryID", "Nome"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Start] >= #datetime(2017, 1, 1, 0, 0, 0))
in
    #"Filtered Rows"

      This is the same code with your suggestion:

       

      let
    Source = Access.Database(File.Contents("D:\Users\tljordao\OneDrive - SONAE\Relatorios Individuais\Carla Costa.mdb"), [CreateNavigationProperties=true]),
    #"_Carla Costa" = Source{[Schema="",Item="Carla Costa"]}[Data],
    #"Added Custom" = Table.AddColumn(#"_Carla Costa", "Nome", each "Carla Costa"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Start", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"EntryID", "Nome"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Start] > Date.From(Date.StartOfMonth(DateTime.LocalNow() )))
in
    #"Filtered Rows"

      Error (in Portuguese):

      «Expression.Error: Não é possível aplicar o operador < aos tipos Date e DateTime.
      Detalhes:
      Operator=&lt;
      Left=01-01-2017
      Right=30-08-2016 17:00:00»

       

      This is the last code with the correct DateTimeZone function:

       

      let
    Source = Access.Database(File.Contents("D:\Users\tljordao\OneDrive - SONAE\Relatorios Individuais\Carla Costa.mdb"), [CreateNavigationProperties=true]),
    #"_Carla Costa" = Source{[Schema="",Item="Carla Costa"]}[Data],
    #"Added Custom" = Table.AddColumn(#"_Carla Costa", "Nome", each "Carla Costa"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Start", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"EntryID", "Nome"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Start] > Date.From(Date.StartOfMonth(DateTimeZone.FixedUtcNow() )))
in
    #"Filtered Rows"

       

      Same error from before.

       

       

      TJ

       

       

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Apparently [Start] has DateTime format and the error can be prevented with DateTime.From instead of Date.From:

        DateTime.From(Date.StartOfMonth(DateTimeZone.FixedUtcNow()))

         

        Note that the result of DateTimeZone.FixedUtcNow() depends on the timezone on which the code is run, e.g. with me in the Netherlands (CET = UTC +1:00) the beginning of this month (UTC) is 1-1-2017 1:00.

         

        If your data has mixed timezones, then Power Query has no suitable functions to convert those to 1 universal time (UTC).

        There are some functions that allow adding/subtracting zone hours / minutes, but nothing dynamic takling into account DST switches and other clock adjustments (e.g. due to government decisions).

         

        As a last remark: note that all functions that are related to system date/time should be interpreted as "... at the date/time of last query refresh".