skip to Main Content

What I didn’t know about the Power BI Service (PBIS):

I’ve been using the Power BI Service (PBIS) daily for five months, and before that, I consulted on challenging DAX problems for over a year. So, when I attended a Dashboard in a Day training put on by KnowledgeLake at my local Microsoft office, I didn’t expect to learn a lot, but I did hope to make some good contacts with fellow data wranglers in Kansas City. There was, however, one thing that surprised me: Get Data within the Power BI Service.

Note: This article requires the use of Power BI Pro, Office 365, and SharePoint Online (it’s ok if SharePoint on premises is your main intranet).

Get Data (Power BI Service) vs. Publish from Desktop

Publish from Desktop

There are two ways to get reports into the Power BI Service: (1) Publish in PBI Desktop, and (2) Get Data from Power BI Service. I have been relying on the first approach, publish. I would refresh locally,  save my Power BI file, and then press the Publish button in the ribbon. And then I would wait as the report published on the service.

Refresh and Publish - Power BI Desktop

I used to use these buttons ALL the time. Now, not so much…

Get Data from Power BI Service

The second option is to save the file to OneDrive, or to a SharePoint team site, and then use Get Data to load the file. I confess I didn’t immediately grasp the benefits of this second method. But then I remembered something Krissy had mentioned to me about Power BI files sourced from SharePoint being automatically updated. I had been storing my files in a SharePoint team site, opening them, and publishing them to PBIS, but this doesn’t give you the benefit. To get the benefit of auto-updating, the SharePoint files must be sourced from PBIS via Get Data. 

First, save your file to SharePoint

I prefer to sync my SharePoint folders and save to my local copy. But you could also upload them directly to SharePoint.

Start on the Reports tab of the Power BI workspace

Get Data Report Power BI Service PBIS

The meaning of a button can change depending on where you are when you click it (Microsoft rules). If you have the Reports tab active when you click it, the Power BI file will upload as a Report. If not, it will upload as a dataset. Good to know!

Get Data; then click Files.

Get Data Power BI Service PBIS 1 2

Next, click SharePoint Team Sites

Get Data Power BI Service PBIS 3

As you can see above, OneDrive can be used as an online source similarly.

Lastly, put in the team site URL

get data power bi service pbis 4 site url

Now, it says you can click connect without a URL… but I recommend having the team site URL so you won’t have to hunt for the file. 

Get Data Power BI Service Sharepoint PBIS 5 6

You then select your Power BI report and upload it to the service.

This is when the magic happens. The shared report on the Power BI Service will be updated whenever the original file on the team site is updated. AND, any SharePoint data sources in the file will also be automatically updated hourly as well.

Read the details from Microsoft here. The only thing I would add to the documentation is what I’ve shown above: how to source a report from SharePoint.

Keeping Reports in SharePoint Team Sites

I like to keep my Power BI source files in SharePoint team sites. It’s the logical place to look when someone else wants to review or update the report. And typically, the SharePoint team site has the same name as the Power BI app workspace. I set up the SharePoint site to sync with my local machine so that I don’t have to upload and download the file every time I make changes. Instead, I just edit and save the file. Syncing to the site happens automatically, and then the report in the workspace is also updated automatically (within an hour, but often just a few minutes).

SharePoint Online Versioning

Get Data Power BI PBIS - SharePoint versioning

SharePoint Online has built-in versioning to track changes in files. Click on the three dots menu as shown to view the version history. If there’s a problem with a recent version, you can restore a previous version. A copy of the selected version becomes the most recent version of the file. If you don’t notice a mistake immediately, versioning can save you from hours of fixing a report. One time, I merged in a new source, and it created duplicates. I also made other changes after that so when I discovered the problem; I was able to peel back the versions to discover the culprit. 

Check out and Check in

Get Data Power BI PBIS - SharePoint Check out

Hidden in the drop-down menu under More is the Check out option. Check out locks the file so that nobody else can save changes to the file. Combined with team communication, it can be useful in team environments. After a file is checked out, it can be checked back in.

Get Data Power BI PBIS - SharePoint Check in

Check in is great, even in non-team environments. Why? Because it enables you to describe the changes made. 

Get Data Power BI PBIS - SharePoint Check in comment

Sometimes, I’ll check out a file and check it in just to record the comment. 

Get Data Power BI PBIS - SharePoint Versions Check in comments

Making comments to document significant change makes versioning even more useful. You can quickly see which version you need.

Note: if you are saving your file locally and syncing, it takes a few minutes for changes to make it to SharePoint Online.

Publishing the workspace to the app

I use Workspace apps to make reports available to the users of the report. When publishing from Power BI Desktop, you can update the app immediately. When relying on the automatic update, you need to:

  1. Allow the local file to sync to SharePoint Online;
  2. Allow the report to update in the workspace;
  3. Manually refresh now or wait until the next scheduled data refresh to happen (make sure your credentials are valid, especially for SharePoint sources).

Once the app and the data are up to date, the app can be republished. Updating the app is a two-step process: 1. Update app and 2, Update app.

Step 1: Update app

Step 2: Update app

Get Data Power BI PBIS - Update App 2

What else?

For more details on publishing apps in Power BI Service, see Microsoft’s documentation: Distribute to large audiences using Power BI apps. I also recommend SQL Chick’s Checklist for Finalizing a Model in Power BI Desktop. The checklist is focused on the model rather than the service, but it includes many crucial considerations that will impact a published report.

Human, and Tech*

We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”

In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂

* – unless, of course, you have two turntables and a microphone.  We hear a lot of things are also located there.

Fred Kaffenberger

A teacher at heart, Fred loves that moment when someone struggling to solve a problem makes a breakthrough. Fred spent six years in client services and sales for an online work order system. After this, he used Excel to help streamline commercial real estate operations for a major telecommunications carrier, where he discovered Power Pivot and Power BI. He was thrilled at how these tools helped him work smarter and more systematically. As an English major, Fred knows that a knack for working with data can surface in surprising places.

This Post Has 12 Comments
  1. Thanks for the article; i found it very interesting.

    My client organisation hasn’t yet made the jump to Power BI yet (although we use PowerPivot and PowerQuery heavily) as, from our understanding, there is not a way of ‘opening’ up the dashboards to colleagues without a Power BI license. The client has 400 employees so it is cost prohibitive. We are on Office 365 E3 license (so Power BI is not part of our plan) and have SharePoint Online.

    In an ideal world we’d have a number of licenses for dashboard developers and would publish these to SharePoint Online for the business to use…. is that possible on the current licensing model? From what I’ve read and heard the answer is no; it would appear all ‘consumers’ of the dashboard would need a license. I’ve spoken to Microsoft reps but no one is 100%….maybe I’m not articulating my requirements correctly!

    Am I understanding that correctly or does the ability to publish ‘apps’ allow my colleagues with an office 365 license (but not a Power BI license) to access and use the dashboards?

    Many thanks!
    James

    1. James,
      You are correct. For colleagues to view the reports and dashboards (in SharePoint Online, Teams, Salesforce) they need a Power BI Pro license. Or, you need Power BI Embedded backed up by Azure Capacity.

    2. Report consumers need Power BI Pro licenses too (licensed separately or as part of O365 E5), as per licensing changes introduced in May last year. This is the case whether you send your users to the Power BI service or embed PBI content in Sharepoint pages.

    3. Large organisations can purchase a Power BI Premium License which allows those who have Power BI Pro to distribute dashboards / reports to end users through the App. The end users then can interact with the content via a Power BI Free license. In this instance, content creators would need a Power BI License, content consumers are covered under the Premium license. Microsoft is also looking at making it possible to share specific users outside your organization if you have a Premium license.

      1. Meant to say – In this instance,content creators would still need a separate Power BI Pro license and content consumers would be covered under the Premium license.

  2. Hi Fred, thanks for the article, it was very insightful. I have an issue that is a little different because we built a data model in Excel and would like to use it to create Power BI Desktop reports without having to create separate data models. Based on your article, we would need to save the the Excel file in Sharepoint, publish it to Power BI online, and then use get data from service in desktop in order to use a synced data model?
    Thanks!

    1. Wendy – yes, that’s it!

      Use get data from the Dataset tab in PBIS. And, you can also get the whole Excel workbook so that it will show up in the workbooks tab of the service. It’s a hidden form of Excel Online that is similar to publishing a Power Pivot workbook via SSAS Tabular.

  3. “If you have the Reports tab active when you click it, the Power BI file will upload as a Report. If not, it will upload as a dataset. Good to know!”

    Are you sure? I always get both, report and dataset.

    Maybe I’m missing something.

  4. I’m revisiting this workflow as we’re setting things up that way with one of my clients (I was the only PBI author until now). Synchronizing PBIX files with a SharePoint document library using the OneDrive for Business desktop client works great, but as far as I know there’s no way to input a SharePoint versioning comment from OneDrive on the desktop (File Explorer really). Once in SharePoint there also doesn’t seem to be a way to add comments to file versions after the fact. So you need to check out the file, check it back in with a comment, and optionally erase the previous version to keep things tidy (but then there are skipped version numbers in the file history). That’s a bunch of extra clicks which makes me think there should be a better way. Is there something I’m missing?

  5. Thanks Fred for this great article. Our company is trying to decide where the best place is to host our Power BI reports, Excel worksheets, Excel models and Power Pivot models. We have SharePoint Online and we’re also going to be purchasing Power BI Premium (one node). It’s not clear to us if it would be better to host these assets on Power BI Service or on SharePoint, and how to make that decision.

Leave a Comment or Question