Forum Discussion

Hgoonetilleke's avatar
Hgoonetilleke
Regular Visitor
3 years ago
Solved

mapping data in multiple columns from another table

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 IDEquipment Tag

1000

ABC-01
1001DEF-02
1002

ABC-03

1003JKL-04

 

TABLE B

Equipment Name

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

 

OUTPUT

Equipment IDEquipment TagEquipment Name

1000

ABC-01B
1001DEF-02C
1002

ABC-03

E
1003JKL-04A

 

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

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

3 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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.