Forum Discussion
Using a mapping table to replace multiple values in multiple columns
Hi all,
I'm trying to use a mapping table to replace multiple values in multiple columns. My table looks like this
| Person | Question1 | Question2 | Question3 |
| Juan | Si | No | Si |
| Joe | Yes | Yes | No |
| Joel | Nein | Ja | Ja |
=Table1
And my mapping table like this
| Old | New |
| Si | Yes |
| No | No |
| Nein | No |
| Ja | Yes |
| Yes | Yes |
= Table2
My data is a consolidation of a survey in multiple languages and now I would need to translate the Yes and No's of all languages to English in order to make sense of it. What I have been trying to do is use Replace Values to replace the other languages with English using this command:
= Table.ReplaceValue(#"Changed Type", each Table2[Old], each Table2[New] ,Replacer.ReplaceText,{"Question1", "Question2", "Question3"}), but nothing happens. I know the method of mergin and adding a new column, but that is not really scalable in the same way as this would be. I have multiple rows and adding and removing those would be a lot of work. In this when I add a new column, I simply add another column name to the end. Here is a link to the example file used above.
Best regards,
Christian
8 Replies
Hi Anonymous
In Edit queries,
In Table 1
1.Click on "Person" column, Unpivot other columns, then get columns: "Person", "Attribute","Value"
2.Merge queries in Table 1, expand "old" and "new" columns from the queries.
3. add a conditional column
4.remove columns: "Value", "Table2.Old", "Table2.New"
5. Ctrl+select "Attribute" and "Custom" columns, Pivot columns
Finally
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
Thanks for the answer! This looks like quite a potential solution. However, in my real data I have other columns then "Person" that shouldn't be touched as well. This solution might work there as well but I'm a bit afraid to use it as it could mess up things. I need to check if it's possible.
Two questions:
1. Is there anyway to get my way to work? It would be a nice way without a lot of extra steps to the data.
2. Do I always have to make a new query when merging or can I merge things within the same query?
Br,
ChristianHi Anonymous
If you have many columns like
person question1 question2 question3 other1 other2 other3
Just Ctrl+ select "question1 question2 question3" these three columns, if you have question4, 5, please select all question columns,
then select "Unpivot columns"->unpivot only selected columns.
Other steps are the same as my previous reply said.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
= Table.ReplaceValue(#"Changed Type","","",(x,y,z)=>Table2{[Old=x]}?[New]? ??x,{"Question1", "Question2", "Question3"})how does this function work?
I'd like to take only the first 5 characters of the values, compare them with "old" and if they match take the value "new", otherwise keep "old". Is this possible by using the (x,y,z) function?
