Power Query conditional filtering

[Anonymous]

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 date is: 2021-Dec-06

then I need data covering period: 2021-Sep 1 - 2021-Nov 30

In case data avaialble for the full month for example: latest available date is: 2021-Oct-31 then I will need data covering period: 2021-Aug 1 - 2021-Oct 31

I hope I could explain my problem clearly.

Thanks a lot for your help
Peter

[mahoneypat]

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]

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 · Accepted solution]

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]

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

[BA_Pete]

Hi @Anonymous ,

If you just want the last three complete months, then you can use the GUI to filter you date column:

In the dialog that opens, enter your quantity (3) and period type (months). This will only retain complete periods.

Pete

[Anonymous]

Hi @BA_Pete ,

Thanks for your reply. I forgot to tell you that as new data feeds into the table every day this approach wouldn't be dynamic.

I need to check at data refresh what is the last day in my data source and then use the logic I described before.

Thanks

Peter