Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
skumars78
New Member

Filter Data in Power Query Dynamically

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:

skumars78_0-1674520319889.png

 

Sample 2:

 

skumars78_1-1674520374141.png

 

Thanks,

Phani

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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

vjingzhang_0-1674633809752.png

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

vjingzhang_1-1674634000729.png

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.

 

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

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

vjingzhang_0-1674633809752.png

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

vjingzhang_1-1674634000729.png

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.

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.