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 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

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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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
        Microsoft 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

  • 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