Power Query - Replace values based on another table's data

[mariomaraucci]

Hi, I am new to the Power Query world and this community 🙂

I'm struggling with a task -  I have a table "All_Skills" that contains 2 columns like this:

Skill	Center
Licensing	Other
Billing Inquiries	Billing
Windows support	Computer Software
Linux support	Computer Software
Payment denied	Billing
Laptop replacement	Computer Hardware

Then, I have another query that generates a new table, and the "Interaction" column can contains the same values listed in "All_Skill[Skill]".

What I want to do is to replace all the values in a duplicate of "Interaction" based on the matched value in All_Skills[Center]", like:

Interaction	Interaction_Center
Licensing > Billing Inquiries > Payment denied	Other > Billing > Billing
Billing Inquiries > Laptop replacement	Billing > Computer Hardware
Linux support > Laptop replacement	Computer Software > Computer Hardware

I tried using this, but doens't seem to be working, although I'm not getting any erorr:

= Table.ReplaceValue(#"Duplicated Column",each All_Skills[Skille],each All_Skills[Center],Replacer.ReplaceText,{"Interactions_Center"})

I know I could split the Interaction column by delimiter, then merge every output column with the existing table and get the Center, then merge again the columns, but it can also get 20-30 output columns and I'd like to avoid all these merges.

Any ideas? I hope there will be a solution to this, and I definitely need to study more 😅

Thanks!

[jennratten · Accepted solution]

Hello - this makes perfect sense.  Please have a look at the attached example, which demonstrates how to use a mapping table to perform multiple replaces in other tables.

[Vijay_A_Verma · Accepted solution]

Use a custom column and put following formula there

= Text.Combine(List.Transform(Text.Split([Interaction],">"),(x)=>try All_Skills{[Skill=Text.Trim(x)]}[Center] otherwise null)," > ")

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slMTs0rzsxLV4gpNTAwTj20wCkzJwfE98wrLM0sykwthssEJFbmpuaVKKSk5mWmpijF6kQr4Vbsk1hQkl+gUJRakJOYnArSB9bgk5lXWqFQXFpQkF9UgldxLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Interaction = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Interactions_Center", each Text.Combine(List.Transform(Text.Split([Interaction],">"),(x)=>try All_Skills{[Skill=Text.Trim(x)]}[Center] otherwise null)," > "))
in
    #"Added Custom"

[mariomaraucci]

This is exactly what I needed! I'm still trying to understand WHY it works (😂), but once I'm free I'll dive into it to understand it fully!

Thanks so much, this helped me a lot!

[jennratten · Accepted solution]

Hello - this makes perfect sense.  Please have a look at the attached example, which demonstrates how to use a mapping table to perform multiple replaces in other tables.

[mariomaraucci]

This is brilliant! It also just initiated myself to Power BI, so far I was only working in Power Query 😃

Thank you very much!