Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have data that comes that gets added in this format mainly : Year and Category.
What I want is to to filter out data basing the field Category ="Act" , so that is suppose if any Year has got a Category = ACT it must filter out the rest .
For example , Year = 2017 has ACT,BUD in Category , so the output after filteration or formula must be only 2017 ACT and not 2017 Bud.
I really appreciate if the solution in given in the query editor or also dax.
I cannot do a manual approach since users keep adding data .
@BhaveshPatel Phil_Seamark @Sean
Regards,
Ravi
Solved! Go to Solution.
Hi,
Here is one way how to solve it in Query Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFPSUQpyU4rVgfMcnUNgXHNMrlNpCoxrgSpriWKSJUxtLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Category", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Year"}, #"Changed Type", {"Year"}, "Changed Type", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.Column([Changed Type], "Category")), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Changed Type"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "ContainsACT", each if Text.Contains([Custom], "ACT") then 1 else 0), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "FilterColumn", each if [Category] <> "ACT" and [ContainsACT] = 1 then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([FilterColumn] = 0)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "ContainsACT", "FilterColumn"}) in #"Removed Columns1"
Hi,
Here is one way how to solve it in Query Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFPSUQpyU4rVgfMcnUNgXHNMrlNpCoxrgSpriWKSJUxtLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Category", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Year"}, #"Changed Type", {"Year"}, "Changed Type", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.Column([Changed Type], "Category")), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Changed Type"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "ContainsACT", each if Text.Contains([Custom], "ACT") then 1 else 0), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "FilterColumn", each if [Category] <> "ACT" and [ContainsACT] = 1 then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([FilterColumn] = 0)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "ContainsACT", "FilterColumn"}) in #"Removed Columns1"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |