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

[Anonymous]

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!

[Anonymous · Accepted solution]

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]

@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 

[freginier]

Add new column with this formula 

https://docs.microsoft.com/en-us/powerquery-m/date-isinpreviousmonth

Then if is previous month then filter it