Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query exclude dates in previous calendar month and current incomplete month

Hullo, 

I have an Azure dataset that pulls data from the previous calendar month, and current incomplete month. So as today is January 19th 2022 - the dataset holds December 2021 and the first half of January 2022. Next month, it will drop December 2021 and so forth.

I also have a data pull of historic Azure data which holds 12 calendar months. But, I need to EXCLUDE the previous calendar month so I don't duplicate volumes. I can't manually exclude December 2021, because next month I will need to exclude January 2022 and so forth. 

Please help!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Add a custom column:

    column = if Date.StartOfMonth([date]) > Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -1))) then 1 else 0

    Then filter the table by custom column.

     

    Best Regards,

    Jay

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Add a custom column:

    column = if Date.StartOfMonth([date]) > Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -1))) then 1 else 0

    Then filter the table by custom column.

     

    Best Regards,

    Jay

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try to add filter like

    = Table.SelectRows(#"Changed Type", each [Column1] < Date.StartOfMonth( Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)))

     

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

     

    example code