Easy look up but don't know how to do this between two tables

[Anonymous]

I am too new to PBI. I have the following table structure:

TABLE A

ID_TYPE	ID	INDUSTRY
SEDOL	ads12342	TABLE B[INDUSTRY]
TICKER	XYZ	TABLE B[INDUSTRY]

In another table I have a look up of values:

TABLE B

ALIAS_ID	TICKER	SEDOL	ISIN	INDUSTRY
1XS	XYZ	123asds	FFFAAASS	Food
1XY	DDS	ads12342	DSDS	Heavy Metal

What I want to do is look up from TABLE B the value based upon what ID Type it is on TABLE A and then on TABLE A[ID] and put the industry on that table as a calculated column.

Should I do a merge query in Power M or a calculated lookup? What is the best way to do this?

[Anonymous · Accepted solution]

Super clever on the key. That must be years of DW experience. I just saw this in another implementation. Sorry for being such an amateur.

[Greg_Deckler]

@Anonymous Yep, if you unpivot those two columns in Table B then you could do a merge with Table A and Table B. Easier just to attache the PBIX file (below)

[Greg_Deckler]

@Anonymous Well, you can do this in DAX like the following:

Column = 
  SWITCH('TableA'[ID_TYPE],
    "SEDOL",LOOKUPVALUE('TableB'[Industry],'TableB'[SEDOL],'TableA'[ID]),
    "TICKER",LOOKUPVALUE('TableB'[Industry],'TableB'[TICKER],'TableA'[ID])
  )

Might be able to do something equivalent in M with if then else. I am going to look at one other option, unpivoting those two columns.