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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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