Hi, I am trying to find a flow to solve my problem which requires data pivoting & lookup per Theme items.
My starting point is an Input table where particular Theme has multiple Level Codes & their corresponding Names. Each Code has some number of characters but values can start from Level 1 or Level 2,3,4,5. Final one is 5. Then the following Level Code has +1 character length.
What I am trying to do is to extract only 2 characters length value per each Theme and then derive what is its corresponding Level (Number) Code and Level Code Name.
Having in mind that the most granular is Level 5 Code I can create new column which has 2 characters length value based on Level 5 Code filling. However my concern is how to lookup which column from Input has this value & then derive its corresponding Name.
Input
| Theme | Level 1 Code | Level 1 Code Name | Level 2 Code | Level 2 Code Name | Level 3 Code | Level 3 Code Name | Level 4 Code | Level 4 Code Name | Level 5 Code | Level 5 Code Name |
| Apple | A | Europe | AH | Middle Europe | AHD | Poland | AHDG | Warsaw | AHDGN | Ochota |
| Banana | G | Asia | GD | East Asia | GDT | Japan | GDTX | Tokio | GDTXL | Ginza |
| Tomato | (blank) | (blank) | B | Africa | BF | North Africa | BFT | Egypt | BFTO | Cairo |
| Pasta | (blank) | (blank) | (blank) | (blank) | Y | North America | YK | US | YKS | California |
Desired output (output might have all input data as well, my focus is on 3 columns after theme):
| Theme | 2 characters Code | Correponding Level | Correponding Code Name |
| Apple | AH | Level 2 Code | Middle Europe |
| Banana | GD | Level 2 Code | East Asia |
| Tomato | BF | Level 3 Code | North Africa |
| Pasta | YK | Level 4 Code | US |
Any ideas will be much appreciated.
