Filter Data in Power Query Dynamically

[skumars78]

Hello Team - I have a dataset in my Power BI report that uses CSV file as source. One of the column is Quarter and the other column is Amount. This would have data from last 4 quarters till current quarter. Current quarter data would be updated only in 3rd or 4th week after the quarter starts. If current quarter data doesn't exist, I want to retain only last quarter data. If exists, I want to retain only current quarter data. I can derive current quarter or previous quarter based on current date. But how can I filter out the unwanted records? I would like to implement this in M level not at DAX level. Please help.

Sample 1:

Sample 2:

Thanks,

Phani

[v-jingzhang · Accepted solution]

Hi @skumars78 

You can add two custom columns with below code. Notice: change the "previous step name" for the second column accordingly. 

= [Quarter] = "Q" & Text.From(Date.QuarterOfYear(DateTime.LocalNow())) & " " & Text.From(Date.Year(DateTime.LocalNow()))

= if List.Contains(#"previous step name"[Custom],true) then [Custom] else [Quarter] = "Q" & Text.From(Date.QuarterOfYear(Date.AddQuarters(DateTime.LocalNow(),-1))) & " " & Text.From(Date.Year(Date.AddQuarters(DateTime.LocalNow(),-1)))

Then filter on the second custom column "Custom.1" to keep rows with value TRUE. 

After that, you can remove two custom columns if you don't need them any more. 

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRUMDIwMlbSUXJOLDEEUoYGBkqxOjAJI4gEiDKFipsgiYM0mJtCxI3R1QPFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Category = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", type text}, {"Category", type text}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Quarter] = "Q" & Text.From(Date.QuarterOfYear(DateTime.LocalNow())) & " " & Text.From(Date.Year(DateTime.LocalNow()))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if List.Contains(#"Added Custom"[Custom],true) then [Custom] else [Quarter] = "Q" & Text.From(Date.QuarterOfYear(Date.AddQuarters(DateTime.LocalNow(),-1))) & " " & Text.From(Date.Year(Date.AddQuarters(DateTime.LocalNow(),-1)))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true))
in
    #"Filtered Rows"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.