Forum Discussion
Power Query - Replace values based on another table's data
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!
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.
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"
4 Replies
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.
This is brilliant! It also just initiated myself to Power BI, so far I was only working in Power Query 😃
Thank you very much!
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"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!
