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,
I have a table of format:
null | x | y |
null | null | z |
x | null | null |
null | null | x |
z | z | y |
I want to keep only those rows which contain 'x'. The Pseudocode which I have in mind is:
If each row contains 'x', keep it else remove from table.
Output format:
null | x | y |
x | null | null |
null | null | x |
Can somebody please advise how to achieve this? Thanks!
Solved! Go to Solution.
@anshpalash
Select All the columns (CTRL + A) > Under Add Column Tab, Merge Column, Give a Delimiter and merge.
In the New Column (Merged), Filter Rows Contains X
Code: Paste on a blank Query and check the steps
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSUaoA4kqlWB24AJSqAotVIATAFKa6CrBYFVgH2KRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Col1], [Col2], [Col3]}, "|"), type text),
#"Filtered Rows" = Table.SelectRows(#"Inserted Merged Column", each Text.Contains([Merged], "x")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Merged"})
in
#"Removed Columns"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
One of the ways is to check whether one of the columns contains "x" value via separate column and then by filtering this column. For example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVFVSrE60UoVCAEwBRJDVVcBFqsC69BRqlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"null" = _t, x = _t, y = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Added Custom" = Table.AddColumn(#"Demoted Headers", "Custom", each if [Column1] = "x" or [Column2] = "x" or [Column3] = "x" then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
#"Filtered Rows"
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
One of the ways is to check whether one of the columns contains "x" value via separate column and then by filtering this column. For example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVFVSrE60UoVCAEwBRJDVVcBFqsC69BRqlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"null" = _t, x = _t, y = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Added Custom" = Table.AddColumn(#"Demoted Headers", "Custom", each if [Column1] = "x" or [Column2] = "x" or [Column3] = "x" then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
#"Filtered Rows"
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@anshpalash
Select All the columns (CTRL + A) > Under Add Column Tab, Merge Column, Give a Delimiter and merge.
In the New Column (Merged), Filter Rows Contains X
Code: Paste on a blank Query and check the steps
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSUaoA4kqlWB24AJSqAotVIATAFKa6CrBYFVgH2KRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Col1], [Col2], [Col3]}, "|"), type text),
#"Filtered Rows" = Table.SelectRows(#"Inserted Merged Column", each Text.Contains([Merged], "x")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Merged"})
in
#"Removed Columns"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.