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"