2d Merge (find and replace)

[Anonymous]

Hello!

I am running into this issue where I have this table with several numbers and need them to be lookedup in another table. Essentially, what I have is a couple tables like the following:

Main Table

Name | Value 1 | Value 2 | ... | Value 3 |

Bob    | 5           | 4           | ... |  7          |

Joe     | 4           | 4           | ... |  5          |

Steve  | 1           | 2           | ... |  3          |

Lookup Table

ID | String |

1   | asdf   |

2   | hjkl    |

3   | qwer |

4   | fghj  |

5   | 1234 |

6   | ????  |

7   | @!n1 |

I'm then wanting to merge these into a table like the one below

Name | Value 1 | Value 2 | ... | Value 3 |

Bob    | 1234     | fghj       | ... |  @!n1   |

Joe     | fghj      | fghj       | ... |  1234    |

Steve  | asdf     | hjkl        | ... |  qwer   |

Any ideas how to do this sort of thing?

[Nathaniel_C]

@Anonymous ,

I would copy your table, then use LOOKUPVALUE() to populate the new 3 columns, then delete the columns from the copied table except for name.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C 

The issue with doing it that was is that there are 200+ columns in the real table. I was wondering if there was a more algorithmic solution

[Nathaniel_C]

@Anonymous , Lets reach out to @ImkeF  who is the master of algorithms!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Ok awesome!

As of right now, I have found the only good way to do this was to use Excel to generate the Advanced Editor code. As you can see from this screenshot of the query settings, it's very messy

[ImkeF · Accepted solution]

1) check column "Name"

2) Unpivot other columns

3) Merge on new column "Value" and expand

4) Consider if this table format isn't better suited for your data model than your original idea

optional 5) If you still want your old format, check column "Attribute" and pivot back on column "Value" (don't aggregate)

[Anonymous]

Ah that worked perfectly! 

I totally missed the "dont aggregate" option and was getting stuck on that!

Thank you both so much!