Forum Discussion
Merge and expand in direct query
Hello Community,
I would like to have your assistance in an error that I encountered in direct query mode. I know this is not supported in direct query mode. Atleast, could someone please help me with an alternate way other than importing the data.
Subject: I have extracted two tables separately from two different databases in a server. Now, I would like to merge Table 1, with Table 2 using a related column named Id from table 1 and Status Id from table 2. I did not using merge queries. But the problem is I got table 2 like nested table after merging. Now that I was trying to expand it but encountered an error saying this query is not supported in direct qquery mode and asking me to switch to import query.
Could someone suggest an alternate way to continue in direct query mode?
16 Replies
Max19 Hi!
Instead of merging the tables, use Power BI’s Manage Relationships feature to create a relationship between Table 1 and Table 2 on the Id and Status Id columns.
With the relationship established, you can use DAX formulas to reference and display related values from Table 2 in visuals. This approach allows you to keep the tables separate but still retrieve relevant data for your analysis without merging.
as you can see, in the same visual i've used the ID of Table1 and a Field of Table2.
if it's ok, please accept my answer as solution.
BBF
Hi BeaBF, uzuntasgokberk
The reason I am asking this is, later I will have to do some other transformations like creating custom and conditional columns.
Any comments on this?
Hello Max19 ,
Actually, you can use merge table too. İt is suport query folding some datasource such as MSSQL, SAP BW etc.. But you can use ınner join and left join. İf you'd like to use full outer join that is not support query folding which means also encountered an error saying this query is not supported in direct query.
But other option is making a relationship in the model view. For me I prefer to creating a relationship because it more effective.Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |Thank you so much BeaBF uzuntasgokberk!
I will try and check that and will get back to you!Hey BeaBF / uzuntasgokberk ,
I tried using the following formula in creating a new column, but end up in an error.
Previously, I tried creating tables manually using the necessary columns from the table. It then worked but here, it is not working even though there is a relationship established already. VIEW_RFIS is from a database 1 and rest of the tables from different database2Could you now please help in creating a column in VIEW_RFIS table. That way I can establish realtime data. Else, I will have to manually establish a formula using the data from the necessary column (Statusp[Name]).
Note: please ignore the hidden tables.
