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

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

 

 

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

descalabro wow MarcelBeuganswered that one quickly. I am amazed by the quality & speed of voluntary support offered here on this PBIcommunity. A pat on the back to us all.

 

We could tell your original data that you ingested is of Date/time format as in the manual M code we see #datetime(2017,1,1,0,0,0) Subsequently when filtering the data with the 'dynamic' manner you need to filter using the m code function for Date/Time and thats what Marcels is saying in more down to earth language.

 

In my oroginal example i had ingested date (ONLY) formatted data and hence the M code to filter it was also a function for filtering Dates as opposedd to Date/Time which you need.

 

Cool to see that you guys have also accomodated timezones into your code.

 

I ask my M code expert ImkeF to confirm what we have said, if you are there please ?