Multiple datasets into 1 report

[LiamReidy]

Hello Power Bi Community,

I have a use case where i am trying to create a report in power bi using 2 separate excel files held within a sharepoint site. Both datasets are loaded into my workspace in power bi service. However, I have not been able to load both datasets into a single report. We are unable to use Power Bi Desktop to overcome this due to IT constraints. Is there any way to bring both datasets into a single report?

Thanks,

Liam

[Burningsuit]

Hi @LiamReidy 

This can be done using Datamarts, a new preview feature in Power BI.

Datamarts effectively provide facilities of Power BI desktop in the cloud.

Put simply, in a Workspace, create a Datamart, then use "get Data" to connect to the two Excel files. You can then use the modelling functions to create a relationship between the two Excel files (I'm assuming there is some common field between the two data tables ?) This will create a Datamodel that contains the two tables which you can then create Reports from.

you should read this documentation :  Introduction to datamarts - Power BI | Microsoft Docs

Hope this helps

Stuart

[LiamReidy]

Hi @Burningsuit ,

We dont have PPU or Gen 2 access at the moment. Are you aware of any other workarounds? I tried to create a dataflow in Power Bi Service but it seems this cant be published in a report.

Thanks,

Liam

[Burningsuit · Accepted solution]

Hi @LiamReidy 

You're correct, you can create a Dataflow with a Pro Licence, but you can't create a report from it unless you use Power BI Desktop (or Datamart). You could merge the two tables in the Dataflow, but merging requires a PPU licence or better.  You could of course merge the two tables manually in Excel to create one and report on that but that would be a manual step. If you had Desktop, the Dataflow becomes unnecessary as you can just connect to the two Excel tables. Without Desktop, it seems you have no other option in Power BI. 

Hope this helps

Stuart