Change text within cells of a table that contain a given word for n columns-CodePudding

I have data within a table that occasionally has been inputted with text to say something like not available or No Data etc. I wish to replace each instance a cell contains no that this is then replaced with null across n number of columns. I don't know every type of word that has been entered but it looks as though each cell to be converted to null contains no as characters so I will go with this.

i.e.

Is there any way to combine `if text.contains([n columns],"no") then null else [n columns]

CodePudding user response：

In powerquery, this removes the content of any cell containing (No,NO,no,nO) and converts to a null

Click select the first column, right click, Unpivot other columns

click select Value column and transform ... data type .. text

right click Value column and transform ... lower case

we really don't want that so change this in the formula bar

= Table.TransformColumns(#"Changed Type1",{{"Value", Text.Lower, type text}})

to resemble this instead (which also ignore the Case of the No)

= Table.TransformColumns(#"Changed Type1",{{"Value", each if Text.Contains(_,"no", Comparer.OrdinalIgnoreCase) then null else _, type text}})

click select attribute column

Transform ... pivot column

values column:Value, Advanced ... don’t aggregate

sample full code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type text}}),
#"CheckForNo" = Table.TransformColumns(#"Changed Type1",{{"Value", each if Text.Contains(_,"no", Comparer.OrdinalIgnoreCase) then null else _, type text}}),
#"Pivoted Column" = Table.Pivot(#"CheckForNo", List.Distinct(#"Lowercased Text"[Attribute]), "Attribute", "Value")
in  #"Pivoted Column"