Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 Ye...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-juanli-msft
      Community Support
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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