mapping data in multiple columns from another table

[Hgoonetilleke]

Hi All,

I am trying to map the equipment name from Table B to Table A

See short example below, however the data file im actually using will have 10000+ rows

Table A

Equipment ID	Equipment Tag
1000	ABC-01
1001	DEF-02
1002	ABC-03
1003	JKL-04

TABLE B

Equipment Name	Equipment Parameter 1	Equipment Parameter 2	Equipment Parameter 3	Equipment Parameter 4	Equipment Parameter 5	Equipment Parameter 6	Equipment Parameter 7
A		JKL-04	?	?	JKL-04	????	???
B				ABC-01
C	???	?			?	DEF-02
D			???	?	????
E	ABC-03					?	?
F		?	???	????	?

OUTPUT

Equipment ID	Equipment Tag	Equipment Name
1000	ABC-01	B
1001	DEF-02	C
1002	ABC-03	E
1003	JKL-04	A

The data in Table B is messy, I have used ? to represent random data. there could be duplicate tags, some cells are empty, others will have other numbers and letters

I tried using the 'lookupvalue' which worked well, but seems to only work with one column

I also tried merging the parameter columns and then doing a merge query with Table A equipment tag, but also was not very sucessful.

Any help would be great. thanks

[v-zhangti · Accepted solution]

Hi, @Hgoonetilleke 

You can try the following methods.
Tbale B: Transform-Unpivot Other Columns

Then

Result:

Column:

Equipment Name = 
CALCULATE ( MAX ( 'Table B'[Equipment Name] ),
    FILTER ( 'Table B', [Value] = EARLIER ( 'Table A'[Equipment Tag] ) )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hgoonetilleke]

Thank you

[lbendlin]

Unpivot the data in table B