Using a mapping table to replace multiple values in multiple columns

[Anonymous]

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

[wdx223_Daniel]

= Table.ReplaceValue(#"Changed Type","","",(x,y,z)=>Table2{[Old=x]}?[New]? ??x,{"Question1", "Question2", "Question3"})

[aitorses]

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?

[v-juanli-msft]

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
Maggie

Community 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.

[ImranLi]

Thanks, Maggie, that solution helped me resolve my issue around using a secondary mapping table to replace data in the primary table. 👍🏼😀

[Anonymous]

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,
Christian

[v-juanli-msft]

Hi @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
Maggie

Community 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.

[Anonymous]

Hi!

Thanks for the answer. Do I understand correctly that you are unable to help fix my original formula? The way you have suggested is for sure a good way to do it, but using the way I tried would be very clean and usable in many cases. 

Also, I would at least need an answer to my second question in my previous reply.

Thank you for your help,

Christian

[v-juanli-msft]

Hi @Anonymous 

2. Do I always have to make a new query when merging or can I merge things within the same query?

This is your second question,

you think you may need to create another query instead of making changes in the current query,

for you are afraid "it could mess up things",

right?

Based on my experience, it is no need to create a new query, 

In my example, i make changes in the currect query,

If you worry about  "it could mess up things", you could share a more complex example with me, so that i can give a sample how to avoid messing up things.

Best Regards
Maggie

Community 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.