Replace values in multiple columns with conditional

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.