Custom Column in direct query built and combined with values from another table.

[Anonymous]

Dear PowerBI community,
I am writing to you because I have a problem that I cannot resolve.

I have two tables ( Table A in DirectQuery) and Table B from an Excel Import.
Table A has a column with ID's as well as number of Articles.  the Table B has the same column with ID's and a column of translations
I want to build a new Column in the Table A that for every article in the table A ( that has an ID) to match it with the Translation column of Table B ( by matching with the ID's)

Basically this can work by merging the two queries however this doesn't work.

I tried but the DirectQuery table doesn't show as an option in the "Merge Queries".
I tried using a VlookUp but it's disabled for a directQuery.
Can you help me on this one please?

Thanks a lot!

Kind Regards,
Victor

[StefanoGrimaldi]

worst case escenario make a new table inside the model that its a exact copy of the table A using Dax and them merge them using dax in another new table or doing a new column to that new table C lets say

[Anonymous]

Basically I have this table lets call it B which is the table from import mode. And another: table A, which has artNR and the same column Defect Name.

[StefanoGrimaldi]

ok go by the pictures to look for the option Im telling you: 

them click where it says storage mode and there you will have 3 options: dual, direct query and import, for the dual to work the source must be first in direct query mode and tham it will change to dual, you cant go from import to dual

[Anonymous]

I don;t have this option 

[StefanoGrimaldi · Accepted solution]

them create a new dax table from the direct query table and that shooud let you add the column to that new table

[Anonymous]

I managed, Thanks a lot ! you are golden!

[Anonymous]

I created a new table
newTable= tbRequests( the one that was in directQUery).
Now in the new column I want to use the Lookup to match the defectName of the newTable with the Table B and put as a value the translation, however I cannot lookup between tables

[StefanoGrimaldi]

if tis a sql service analysis it should let you set to dual mode, if it doesnt will appear anyone only that greyed out a unable to select, in direct query mode you cant add columns or queries, for this you do need to set it to import or dual mode, look for the option and let me know if you find it if its greyd out or not (it shouldnt for a sql service)

[StefanoGrimaldi]

go to the relantionship view, and selec the table in direct query, you should be able (as long isnst a direct query of a power bi service dataset) to set the direct query table mode to dual in the properties pane, this will allow to work the tablas a direct and import mode, 

also if you set a relantionship between this 2 table you should be able to use the same effect on a dax for visuals without merging, 

if this helped give some kudos and mark as solution for others to find 

[Anonymous]

The table comes from a SQL service analysis so I think its from a dataset. In the properties pane I don't have the option of dual.
I have tried using a Vlookup however it doesnt work