Replace values in multiple columns with conditional

HI Anonymous ,

1. In Power Query Editor, just select all the columns in which you want to replace Nulls (Ctrl+Click on Windows).
2. Right Click
3. Select Replace Values
4. Enter the relevant the details in the window that appears and click Ok.

This will do the replacement in one step.

Thanks,

Pragati

ithis option does not give you the possibility to do it as conditional. 

imagine i have:

100 nulls

50 P1

40 P2

30 P3

i want to do is a conditional rule saying that change text if the value = null change by "No" else "yes"

this way i change everything in just one step

Hi Anonymous ,

I think it's difficult to do such replacement in a single step.

Below is my whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCjAEEUZKsToILiYHogpKYBVAlzeCGIssj7AILIGdGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1",null,"No",Replacer.ReplaceValue,{"Column1", "Column2", "Column3"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Replaced Value2" = Table.ReplaceValue(#"Unpivoted Other Columns", each [Value], each if [Value] <> "No" then "Yes" else [Value],Replacer.ReplaceValue,{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.