cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 

PersonQuestion1Question2Question3
JuanSiNoSi
JoeYesYesNo
JoelNeinJaJa

=Table1

And my mapping table like this 

OldNew
SiYes
NoNo
NeinNo
JaYes
YesYes

 = 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 8
wdx223_Daniel
Super User
Super User

= 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?

v-juanli-msft
Community Support
Community Support

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.

Capture17.JPG

   3. add a conditional column

Capture18.JPG

  4.remove columns: "Value", "Table2.Old", "Table2.New"

  5. Ctrl+select "Attribute" and "Custom" columns, Pivot columns

Capture19.JPG

Finally

Capture20.JPG

 

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.

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

Anonymous
Not applicable

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

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
Not applicable

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

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.

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors