Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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

1 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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Anonymous
Not applicable

vstoytch_0-1634126471901.png

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.

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

StefanoGrimaldi_0-1634126675614.pngStefanoGrimaldi_1-1634126722628.pngStefanoGrimaldi_2-1634126736639.png

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Anonymous
Not applicable

I don;t have this option 

vstoytch_0-1634126852370.png

 

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Anonymous
Not applicable

I managed, Thanks a lot ! you are golden!

Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

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)





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.