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.
Hello,
Thank you.
Sorry but how should I handle it if there is a scenario where I merge the two columns? Because when I try to merge them together (by fullOuter Join) the scenario looks like below:
| Table1.Col1 |
| ABC |
| DEF |
| GHI |
| JKL |
| MNO |
| PQR |
| Table2.Col3 |
| ABC |
| null |
| GHI |
| JKL² |
| null |
| PQR³ |
| Table1.Col1 | Table2.Col3 |
| ABC | ABC |
| DEF | null |
| GHI | GHI |
| JKL | null |
| MNO | null |
| PQR | null |
| null | JKL² |
| null | PQR³ |
The output I get when trying to replcae value is like below.
| Table1.Col1 |
| ABC |
| DEF |
| GHI |
| JKL |
| MNO |
| PQR |
| JKL² |
| PQR³ |
It has the entries without the superscripted value as well. But the desired output is:
| Table1.Col1 |
| ABC |
| DEF |
| GHI |
| JKL² |
| MNO |
| PQR³ |
How can this be achieved?
I think you may want to explain some more context around your question. I can answer for the example you gave but I don't know if it will solve your real problem.
Do Table1 and Table2 have the same number of rows in the same order like in your example? Is the desired output a new table or a modification to Table1? Do Table1 and Table2 have other columns?
Hi,
Table1 and Table2 do not have the same number of columns. Table1 has 200 records and Table2 has 150.
The desired output is modification of the column in Table1. I want Table1.Col1 to be replaced with the values of Table2.Col3.
Table1 has other columns as well.
I want to replace the values. But I cannot find the right solution to do so.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.