How to transform data from different databases with different security and schemes?

[AlexNL]

Hi guys,

Question about Power BI Desktop concerning developing semantic layers. Hopefully somebody out there can help me.

If you want to combine data from two different databases that are hosted on separate database servers with different user-password combinations, you can make a composite pbix-model to link the two different semantic layer for each source. So far so good.

However, what if you need to perform for example a UNION between two tables, each coming from different semantic layers? Power Query does not work within a composite model in this case. How can I achieve this?

Also I want to develop as cleanly as possible without creating unnecessary complexity in my semantic layers. Is this even possible, or does it inevitably lead to messy workarounds?

Thank you!

[v-csrikanth]

Hi @AlexNL 

We are following up once again regarding your query. Did you gat any chanve to raise it in the ideas forum with Microsoft. 

As per Microsoft community guidelines, we will now proceed to close this thread to keep discussions focused and manageable. If you still need assistance, you're welcome to start a new thread in the community at any time.

We appreciate your understanding and participation.

Thank you for your understanding and participation.

Best Regards,
Cheri Srikanth

[Anonymous]

Hi @AlexNL,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

[AlexNL]

Hi @Anonymous 

Unfortunately there is no Power BI only solution for it according to the replies.

[Anonymous · Accepted solution]

Hi @AlexNL,

Thank you for reaching out in Microsoft Community Forum.

You're right: currently, Power BI doesn't support combining tables from different sources (like UNION) directly in Power Query within a composite model. While there are some workarounds (like using DAX or external views), there's no true "Power BI only" solution for this at the moment.

This is a great feature idea, and I’d definitely recommend submitting it on the https://community.fabric.microsoft.com/t5/Ideas/ct-p/PBI_Comm_Ideas  -  it's feedback like this that helps drive improvements.

Please continue using Microsoft Community Forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[AlexNL]

Thank you for reply. The answer is clear. I will submit is as a feature idea. 🙂

[Anonymous]

Hi @AlexNL,

Thank you for submitting the feature idea. if my posts are helpful. please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[Anonymous]

Hi @AlexNL,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[Deku]

You can with table.union() but all the it will break query folding. You will need to investigate the privacy levels set on each source.

https://support.microsoft.com/en-gb/office/set-privacy-levels-power-query-cc3ede4d-359e-4b28-bc72-9b...

[AlexNL]

I checked. Unfortunately it does not have any effect.

[Anonymous]

Hi @AlexNL,

Thanks for reaching out in Microsoft Community Forum.

Since the privacy levels check didn’t resolve the issue, please follow below steps to resolve the error;

1.Please Check Query Folding using below steps;
In Power Query, right-click on the steps and see if "View Native Query" is available. If not, query folding is being broken at that step. You can also enable Power Query diagnostics to help identify where folding breaks.

2.Load each source table separately into staging queries and then try to combine them later in Power BI. This can sometimes help avoid query folding issues.

3.Please create a SQL view or stored procedure on the database side to perform the UNION before the data is brought into Power BI. This ensures better performance and avoids the folding problem.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[AlexNL]

Thank you for answer. I understand answer 2 and 3, but I am looking for a Power BI only solution. I am afraid it is not possible. To be sure I made a simple picture just to be sure you understand the question. THANK YOU.

[Anonymous]

Hi @AlexNL,

Thanks for reaching out in Microsoft Community Forum.

Please follow below to resolve the error;

1.If Power Query doesn’t support UNION, you can create a new table in Power BI using DAX:
CombinedTable = UNION('Table1', 'Table2')

2.If the data sources are cloud-based, using Power BI Dataflows might allow pre-aggregation before the model loads.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.