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 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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