Combining data sets in Power BI workspace

[markdevries]

It's become clear to me that I cannot combine multiple tables to create a single report within the Power BI Service, that I must use BI Desktop instead, and then upload to BI Service.  So, my question is how do I get that functionality, as well as importing other file type like MS Access, when my team and I are only using Office 365, without locally installed Office products?

I'm very big on Office 365 (I have an E3 subscription), but am now realizing that the Office products available through the service are much less capable than with locally installed software, particularly Power BI.  How can I make up the difference without installing the full Office products locally?

[v-yuezhe-msft]

Hi @markdevries,

I am not sure that what you really want to achieve by using Power BI Desktop. Based on your description, you want to connect to different items on Office 365 from Power BI Desktop, right?

If that is the case, it is possible. Firstly, you can import Excel files from shared OneDrive for Business folders or from Office 365 group folders, use the URL of the Excel file, and input it into the Web data source in Power BI Desktop. For more detailed steps, please review this article.

Secondly, you are able to connect to office 365 Sharepoint Online via the OData feed option of Power BI Desktop, which is described in this article.

Thirdly, based on my research, you are able to build Access database in Office 365 as you have Office 365 E3 subscription. You can check the following blogs for more information.

http://www.techrepublic.com/article/microsoft-drops-the-ball-access-2016-not-included-in-office-365/
http://thenextweb.com/microsoft/2014/02/03/microsoft-announces-general-availability-access-apps-offi...

Once you have available Access database in Office 365, you should be able to connect to the database using URL from Power BI Desktop.


Thanks,
Lydia Zhang

[markdevries]

Hi Lydia,

Thanks very much for your response!

I've used the Power BI Service on Office 365 to load/import simple Excel files, and that works great.  But, I have several different Excel files/tables which I wanted to combine into one BI Report or Visual, linking them by a common key field.  But BI Service cannot combine multiples tables, only BI Desktop can do that.

So, I thought I would just load all of the tables into one Access file and import that into BI Service.  (I previously created the Access file using a local copy of Access on a PC)  But, I discovered that BI Service doesn't import Access files either, only BI Desktop will do that.  I was very surprised at the extremely limited range of files which BI Service will import.

So, it appears that Office 365 users are very limited when using just BI Service, that they really need to install BI Desktop locally to do much with BI.  I've encountered similar limitations with other Office 365 applications, which have much more limited functions than the locally installed app.  For myself, I always prefer to have the Office apps installed locally.  But, I was looking for a solution like Office 365 for others which can be used from any device, anywhere, through a browser.

In contrast, I also use Tableau for BI as it is my company standard self-service BI tool.  Whether I'm using Tableau Server or Tableau Cloud, I get pretty much the same capabilities as with Tableau Desktop, so I really don't need to install Tableau Desktop locally.  So, I just need to continue looking for the best solution.

Thanks!

[C01Esso]

Three years ago does this question solved? i have the same issue with tables:

In my company, we have a billing system and it generates too much data per day/per outlet and per sales area so it takes too much time to upload the master data in Excel then refresh my power bi report and publish. But i've created a script for automatic update of separate tables in BI service and it works! This only thing i can't do it to combine those differents tables into one... is it possible to do it now using BI Service? if not i will suggest you add same functions on PBI Desktop to BI Service.

[v-yuezhe-msft · Accepted solution]

Hi @markdevries,

You can compare the difference between Power BI Desktop and Power BI Service by reviewing this article.

Since you want to combine data from multiple data sources, it is better to use Power BI Desktop to create reports and publish them to Service. Power BI Desktop provides advanced data exploration, shaping, modeling, and report creation comparing with Power BI Service.

Thanks,
Lydia Zhang

[markdevries]

Thank you very much, Lydia, for your advice and the link to that article!  I probably should have read that first before investing so much time and effort into Office 365.  I will continue to use Office 365, but more as a light-weight collaboration solution, rather than replacing our local desktop apps.  I was hopeful that the cloud solutions today were more mature than they are.  At least I have the kind of Office 365 subscription which provides local app software too.

Thanks!