Keep all rows that contain 'x'

[anshpalash]

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!

[Fowmy · Accepted 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"

[ERD · Accepted solution]

@anshpalash ,

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.

[anshpalash]

Thank you! @ERD 

[Fowmy · Accepted 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"

[anshpalash]

Thank you! @Fowmy