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

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.