Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query conditional filtering

Hi All,   I'm not an expert of PQ and M language, your help is much appreciated.  In my dataset I have a data column where I need to filter for the last 3months of data, but if latest available da...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Please try this instead.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUitWJVjLSN0ZwjPUNTRE8E30kDogJ5cUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Custom1 = let maxdate = List.Max(#"Changed Type"[Date]) in if maxdate = Date.EndOfMonth(maxdate) then maxdate else Date.EndOfMonth(Date.AddMonths(maxdate, -1)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= Date.StartOfMonth(Date.AddMonths(Custom1, -2)) and [Date] <= Custom1)
in
    #"Filtered Rows"

     

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUitWJVjLSN0ZwjPUNTRE8E30kDogJ5cUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= Date.StartOfMonth(Date.AddMonths(List.Max(#"Changed Type"[Date]), -3)) and [Date] <= Date.EndOfMonth(Date.AddMonths(List.Max(#"Changed Type"[Date]), -1)))
in
    #"Filtered Rows"

 

Pat

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks mahoneypat 

This code is close what I'm looking for. I tested it, but found only one slight little problem.
When in my data the last date is actually the last date of that month this code as of now will filter it out and will only include it once the date for 2022.01.01 is available.

 

If there is a way to fix this it would be great, if not it's okay I can wait one more day and once next months date's first date is available I can get previous months full date. 

Test dates:

 

Result of code:

 

As you can see now it's filtering out December completly and one I have 2022.01.01 availble it will add that full month.

Thanks

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Please try this instead.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUitWJVjLSN0ZwjPUNTRE8E30kDogJ5cUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Custom1 = let maxdate = List.Max(#"Changed Type"[Date]) in if maxdate = Date.EndOfMonth(maxdate) then maxdate else Date.EndOfMonth(Date.AddMonths(maxdate, -1)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= Date.StartOfMonth(Date.AddMonths(Custom1, -2)) and [Date] <= Custom1)
in
    #"Filtered Rows"

     

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      mahoneypat 
      Thanks a lot, you're a genius. This is exactly what I was looking for.