How to replace one column value with another column value from another table?

[Kiteretsu]

Hello,

I have this scenario where I have to get the column value replaced from another table column. 

For example:

Let us consider that Column that needs to be replaced from Table1 has 200 records and has no superscript symbols.

Column that replaces the value is from Table2 that has 150 records and has few values with superscript symbols.

Now I want Table1 column values that don't have the superscript symbols to be replaced by the values from Table2 column.
Below is a simple mock-up example of the table columns

Table1.Col1	Table2.Col3	Expected result
ABC	ABC	ABC
DEF	null	DEF
GHI	GHI	GHI
JKL	JKL²	JKL²
MNO	null	MNO
PQR	PQR³	PQR³
STU	null	STU

I do not want to hard-code the superscripts in the query as it may vary depending on the data. 

Please guide me through on how to achieve the expected result to be modified in Table1.Col1 itself.

Thank you!

[v-tsaipranay]

Hello @Kiteretsu,

Thanks for reaching out to the Microsoft fabric community forum.

I hope the answer provided by @dufoq3  resolves your issue.

I am also including a similar thread from the community for your reference. If you need any further assistance, please feel free to reach out.

Solved: Replacing values in one column with values from an... - Microsoft Fabric Community

If my response has addressed your query, please mark the helpful reply and accept it as the solution. This will assist other community members in solving similar problems more efficiently.

Thank you.

[v-tsaipranay]

Hello @Kiteretsu ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[dufoq3]

Hi @Kiteretsu, another solution:

Output

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVorViVZycXUD0+4enmDay9sHTPv6+YPpgMAgMB0cEqoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVorViVZy9/AE017ePoc2gVkBgUGHNivFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col3 = _t]),
    L = List.Buffer({"¹", "²", "³", "⁴", "⁵", "⁶", "⁷", "⁸", "⁹"}),
    T2Filtered = Table.SelectRows(Table2, each List.Contains(L, Text.End([Col3], 1)) ),
    R = Function.Invoke(Record.FromList, Table.ToColumns(Table.AddColumn(T2Filtered, "Custom", each Text.Start([Col3], Text.Length([Col3])-1), type text))),
    Stepback = Table1,
    Ad_Replaced = Table.AddColumn(Stepback, "Replaced", each Record.FieldOrDefault(R, [Col1], [Col1]), type text)
in
    Ad_Replaced

[Kiteretsu]

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?

[AlexisOlson]

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?

[Kiteretsu]

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.

[AlexisOlson]

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.

[Kiteretsu]

This did the work! I used added a new column called flag that tags those with superscripts as Yes and the rest No. 
Removed special characters and then did a ReplaceValue for those that had the superscripts and got the expected results.

[ray_aramburo]

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.

[AlexisOlson]

@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"

[ray_aramburo]

Thanks for the tip! This is more efficient!

[AlexisOlson]

It looks like you can take [Table2.Col3] except where it's null otherwise [Table1.Col1].

Add a new custom column like

if [Table2.Col3] = null then [Table1.Col1] else [Table2.Col3]

There's a nice coalsece operator that makes this simpler to write:

[Table2.Col3] ?? [Table1.Col]