Forum Discussion
How to replace one column value with another column value from another table?
I'd recommend filtering Table2 for only rows with superscripts and adding a column to that filtered table that strips off the super script.
You can then merge this with Table1 on that custom column and expand Col3. Then replace Col1 with Col3 ?? Col1.
Remove Col3 if you like as a final step.
I've added these tables/queries to the file ray shared. See attached.
Note: There are many different ways to solve this problem. I recommend searching power query multiple replace online for additional ideas.
This one's tricky and it requires some steps but here's how I would address it. You need to create first a "flag" to identify which values have a symbol to avoid hardcoding each case. So you can:
1.Evaluate if a value contains a superscript. (Note: I recommend having the superscripts written in a Word or text file because you'll need to copy them. You can use the code as well from the file attached)
2. Then use this flag in a coditional statement within a Replace Value function:
(It might be funky but is the inverse of indicating that if you have a Flag replace the value, it works this way)
And there you go!
Feel free to look at my working file.
ray_aramburo, here's another way to do the first step in your approach that checks superscripts 0 through 9.
if List.Contains({"⁰","¹","²","³","⁴".."⁹"}, Text.End([Table2.Col3], 1)) then "Yes" else "No"Thanks for the tip! This is more efficient!