Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to write M in Power Query that passes the prior step #"Replaced Value", then proceeds to replace the names:
TRACY, MARCY, BARY with TRACI, MARCI, BARI.
I found this code, but get the error: We can not convert a value of type list to type table.
Any thoughts on how to make this work? -- Many Thanks for looking!
Here is my code:
#"SubColVals" =
Table.FromColumns(#"Replaced Value",
let
Source = ({{"TRACY","MARCY","BARY"}}),
Substitutions = [
TRACY = "TRACI",
MARCY = "MARCI",
BARY = "BARI"],
Substituted = Table.TransformColumns(Source,{{"NAME",
each Record.FieldOrDefault(Substitutions, _, _)}})
in
Substituted)
in
#"SubColVals"
Solved! Go to Solution.
Hi @mwaltercpa,
Please download a demo from the attachment that has a solution of other approaches different from yours.
Just at a glance, your code here may not work the way you need. And I think it's hard to replace in place. So I add a column with the desired result. Finally, you can delete the old column.
Since your actual data isn't clear, I created two possible types. Please refer to the snapshot below.
let index = [Index] in List.First(List.ReplaceMatchingItems(Table.SelectRows(#"Added Index", each [Index] = index)[Column1], {{"TRACY", "TRACI"}, {"MARCY","MARCI"}, {"BARY", "BARI"}}))
Text.Combine(List.ReplaceMatchingItems(Text.Split([Column2], ","), {{"TRACY", "TRACI"}, {"MARCY","MARCI"}, {"BARY", "BARI"},{" TRACY", "TRACI"}, {" MARCY","MARCI"}, {" BARY", "BARI"}}), ",")
Best Regards,
Dale
Hi @mwaltercpa,
Please download a demo from the attachment that has a solution of other approaches different from yours.
Just at a glance, your code here may not work the way you need. And I think it's hard to replace in place. So I add a column with the desired result. Finally, you can delete the old column.
Since your actual data isn't clear, I created two possible types. Please refer to the snapshot below.
let index = [Index] in List.First(List.ReplaceMatchingItems(Table.SelectRows(#"Added Index", each [Index] = index)[Column1], {{"TRACY", "TRACI"}, {"MARCY","MARCI"}, {"BARY", "BARI"}}))
Text.Combine(List.ReplaceMatchingItems(Text.Split([Column2], ","), {{"TRACY", "TRACI"}, {"MARCY","MARCI"}, {"BARY", "BARI"},{" TRACY", "TRACI"}, {" MARCY","MARCI"}, {" BARY", "BARI"}}), ",")
Best Regards,
Dale
Hey Gilbert, thanks for the response. Yes i could do that, however my goal is to use this simple example learn to write M and apply this to more complex requirements. Thanks!
Thanks Gilbert, I do have a complex requirment beyond the UI capabilities. I'm keeping this simple for the person that can hlep me work with this. Thanks for your thoughts 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |