Joining multiple data sources into a single data set in PBI Workspace

[cbamf]

Hi,

My team at my company would like to display data from multiple local MS Access databases and from a MS Form in our PBI App. We have successfully worked with our IT department to setup gateways for the Access databases, but we're running into issues because we want to create data relationships between these 3 data sets (i.e. we want to relate tables using shared IDs, not just display data from multiple sources in one dashboard).

My solution to this is to link all data in one MS Access database, then create a gateway from that db to our App's workspace. While this may work, it feels like there might be a better solution, because this requires linking MS Forms data to a local Access file, then re-linking that data to PBI Service.

Does anyone know if there is a more elegant way to link/combine datasets within a PBI workspace?

Thanks,
Charles

[cbamf · Accepted solution]

Hi all,

I resolved this by creating 2 gateways to the two different MS Access DBs, creating the necessary relationships between those data sources and any other data sources I needed in PBI Desktop, then Publishing that report to my PBI Workspace. The associated data set has become my master data set for all further development.

Thanks for the help!

[v-luwang-msft]

Hi @cbamf ，

Has your question solved? the following articles may be helpful for you!

How to Connect Multiple Data Sources in Power BI

Load Data from Multiple Data Sources in Power BI

A First Look at Gen 2 Composite Models with Live Power BI Datasets

Best Regards

Lucien

[GilbertQ]

Hi @cbamf 

have you had a look at the Power BI Composite Models to do this?

Use composite models in Power BI Desktop - Power BI | Microsoft Docs

[cbamf]

Hi Gilbert,

I have read through the provided link, and I am of the impression that this would only work for PBI Desktop. Do composite models or DirectQuery work in PBI Service?

I am attempting to create reports on PBI Service with one-to-many data relationships between data sets located in my team's PBI workspace.

Thanks,
Charles

[GilbertQ]

Hi @cbamf 

It is correct that it will also work in the PBI Service, anything that works in PBI desktop will work in the PBI Service.

You might have to create some data sources using the Gateway or online, but it will certainly work.