Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to Solution.
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
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
Thank you!
@ImkeF , can you help with this
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |