How to create the new column at the "Direct query Table " with using another table

[ChoiJunghoon]

hi, I have to using the "Direct query table". it is big problem.. 

I don't have any choice about this.  

 "Must Use Direct Query".... 

Table A [Direct Query] 

ID
A
A
B
B
C

Table B [Import ] 

ID	TEXT
A	X
B	Y
C	Z

I want to Table C as like below 

ID	TEXT	New Aadd Column to Connect to  another table
A	X	AX
A	X	AX
B	Y	BY
B	Y	BY
C	Z	CZ

[v-lionel-msft]

Hi @ChoiJunghoon ,

(Note: My tableA has one more row of data than yours, so the result is different.)

--table relationship

1. Create a calculated table 'TableC'

TableC = TableA

2. Create two calculated columns.

Text = 
LOOKUPVALUE(
    TableB[Text],
    TableB[ID], 'TableC'[ID]
)

New Aadd Column to Connect to  another table = CONCATENATE( TableC[ID], TableC[Text] )

You can also use ADDCOLUMNS() function to merge formulas together.

Best regards,
Lionel Chen

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

[Anonymous]

Hi @ChoiJunghoon 

Below are the steps I followed to create the required table TableC.

Got o Modelling on the menu bar and select the option as in the screenshot below.

and insert the below code in that formula bar that pops up.

TableC = addcolumns(crossjoin(selectcolumns(TableA, "ID",TableA[ID]),'TableB'),"New Aadd Column to Connect to  another table",CONCATENATE('TableB'[ID],TableB[TEXT]))

And you will see a new table on the Fields section.

[amitchandak · Accepted solution]

@ChoiJunghoon ,

Try this measure with Table A ID and table B text

You can create a power Bi table like this even in direct query mode

addcolumns(crossjoin(selectcolumns(TableA, "IDA",TableA[ID]),TableB),"New COl",[IDA] & [TEXT])

Recommended

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/