Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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.