Forum Discussion

Nie's avatar
Nie
Icon for Helper I rankHelper I
5 years ago
Solved

Conditional Replacement of Values in All Columns

I have a table with values that I want to replace. The number of columns can change over time so I cannot hardcode columns.

 

I know how to change values for 1 specific column but I am not sure how to modify it to be more dynamic. This is a snippet of my code:

 

 

 

Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
ColumnNames = Table.ColumnNames(Source),
Step1 = Table.ReplaceValue(Source,
each [L1_Grade],
each if Text.Contains([L1_Grade], "A") then "Upper"
else if Text.Contains([L1_Grade], "B") then "Middle"
else if Text.Contains([L1_Grade], "C") then "Lower"
else if Text.Contains([L1_Grade], "NaN") then ""
else if Value.Is(Value.FromText([L1_Grade]), type number) then "NumNum"
else [L1_Grade], Replacer.ReplaceText, ColumnNames)

 

 

 

Instead of just replacing values in column "L1_Grade", I want to replace for all columns in the table. How do I modify "each [L1_Grade]" to do so? Or is there a more appropriate way to do so?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Nie ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY2xFcAgCER3oaYwJiIpNb0L+CycIcn+kQgW8IB/x9UK9/O6DRDSqDyKeLTSCzSc0OuOEIPqKCy6m+3QIS0kl0sdpxO/Xyyoej5m/qUGybLEzTSbwWhh7FSRobUP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Name" = _t, L1_Grade = _t, L2_Grade = _t, L3_Grade = _t, L4_Grade = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"L1_Grade", type text}, {"L2_Grade", type text}, {"L3_Grade", type text}, {"L4_Grade", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"L1_Grade", "L2_Grade", "L3_Grade", "L4_Grade"}, "Grade", "Score"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns",each [Score],
    each if Text.Contains([Score], "A") then "Upper"
    else if Text.Contains([Score], "B") then "Middle"
    else if Text.Contains([Score], "C") then "Lower"
    else if Text.Contains([Score], "NaN") then ""
    else if Value.Is(Value.FromText([Score]), type number) then "NumNum"
    else [Score],Replacer.ReplaceText,{"Score"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Grade]), "Grade", "Score")   
in
    #"Pivoted Column"

    Best Regards

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Nie ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY2xFcAgCER3oaYwJiIpNb0L+CycIcn+kQgW8IB/x9UK9/O6DRDSqDyKeLTSCzSc0OuOEIPqKCy6m+3QIS0kl0sdpxO/Xyyoej5m/qUGybLEzTSbwWhh7FSRobUP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Name" = _t, L1_Grade = _t, L2_Grade = _t, L3_Grade = _t, L4_Grade = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"L1_Grade", type text}, {"L2_Grade", type text}, {"L3_Grade", type text}, {"L4_Grade", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"L1_Grade", "L2_Grade", "L3_Grade", "L4_Grade"}, "Grade", "Score"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns",each [Score],
    each if Text.Contains([Score], "A") then "Upper"
    else if Text.Contains([Score], "B") then "Middle"
    else if Text.Contains([Score], "C") then "Lower"
    else if Text.Contains([Score], "NaN") then ""
    else if Value.Is(Value.FromText([Score]), type number) then "NumNum"
    else [Score],Replacer.ReplaceText,{"Score"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Grade]), "Grade", "Score")   
in
    #"Pivoted Column"

    Best Regards