How to Make Power BI Reports More Useful for End Users

By:   |   Updated: 2023-10-30   |   Comments   |   Related: > Power BI


Problem

Throughout my career, there have been several occasions where I was asked how certain common things can be done in Power BI reports on the service to enable the stakeholders, or business end users, to use the reports more efficiently, easily, productively, and readily. Generally, these stakeholders only consume the reports and have no technical understanding of how the reports are created in the Power BI Desktop backend.

This tip will demonstrate how to do several of these common actions in the Power BI service to help the business end user.

Solution

To demonstrate, I have listed below a few common tips based on my experience with what report end users want to be able to do with their reports without requiring IT support:

  1. Bookmark a report page
  2. Analyse the report data in Excel
  3. Export the report to PowerPoint
  4. Embed the report on a Microsoft Teams page
  5. Subscribe to a report

Bookmark a Report Page in Power BI

The bookmark feature in the Power BI service allows end users to bookmark (create a default view) a report page based on their preferences without altering the report view for others. For example, in the report page image below, the display slicer selection on the date slicer is "Last 7 years." However, a user might want to set this to "Last 5 years" by default so that whenever they open the report page, they always see the date filtered to "Last 5 years" unless they change it.

Screenshot showing 7 years slicer selection

To achieve this, while viewing the report in an app or within a workspace in Power BI service, you need to change the slicer you are applying to the bookmark to capture the state of the report you desire. In this demo, I have changed the "Last 7 Years" slicer selection to "Last 5 Years," as seen in the image below.

Screenshot showing 5 years slicer selection

Click the Bookmark icon in the upper right corner. Next, select "Add a personal bookmark" from the dropdown list to create a bookmark for your desired default slicer selection, as seen in the image below.

Screenshot showing how to add a bookmark to a report page in Power BI service

Assign a name to the bookmark. For this purpose, I have named it "Demo Bookmark." Check the box that says "Make default view" if you desire to make this view selection the default view whenever the report is opened. Click "Save." See the image below.

Screenshot showing how to add a bookmark to a report page in Power BI service 2

Analyze Power BI Report Data in Excel

Two ways to export data in reports from Power BI into Excel for further or other analysis include the "Analyze in Excel" or "Export data" features. However, these options export the data to Excel differently.

Analyze Power BI Data in Excel Option

This option downloads your Power BI model dataset to a OneDrive for Business folder (if available). Note: If you are unsure, this download might go into your documents folder in your OneDrive personal mode. However, it would just download as usual, and you can open the Excel workbook in the cloud (Excel Online) as a Pivot Table, which you can then use to analyze your data further if you are used to working in Pivot Tables.

The following steps can be used to export your data to an Excel Pivot Table:

First, at the top left corner of your report page, click the dropdown on the "Export" feature, as seen in the image below. Next, choose "Analyze in Excel" from the options provided. This will download the Excel workbook.

Screenshot showing how to export Power BI report data to Excel to Analyse

Once downloaded, you will see a prompt with a link to click and open the file, as seen in the image below.

Export to Excel button in Power BI service

Once opened, you will see a Pivot Table, as in the image below.

Screenshot of Pivot Table created from Export to Excel action in Power BI service

To trace where your file was saved in OneDrive, click the file name at the top left corner of the file. Click on the defined path to see the file download location if you need to reuse the file again. See the image below.

Screenshot showing how to locate the file path of the Exported Power BI data to Excel.

Export Power BI Data to Excel Option

If you need to export data from a single visual to be further analyzed outside Power BI, then this option is your best choice. You can export the data behind any Power BI visual. However, this feature is best used with Table or Matrix visuals since all the functionalities are actionable on these two visuals.

I will use the matrix visual in the report below to demonstrate how to export data from a matrix visual.

Screenshot showing how ta matrix visual in a Power BI report page

The first step is to hover your mouse over the visual you want to export the data.

While hovering, find and click on the highlighted ellipses (…) at the top right of the visual, as seen in the image below.

Screenshot showing the ellipses(...) on hover over a visual

Select "Export data," as shown in the image below.

Screenshot showing how to export Power BI data to Excel using the Export data option

Next, you will be presented with three options for exporting the data. If exporting from a table or matrix visual, you would only see the first option (Data with current layout).

Screenshot showing how to export Power BI data to Excel using the Export data option 2

The dataset layout you export depends on the option you select at this stage.

If you select the "Data with current layout" option, by default, your data will be exported in Excel (.xlsx) format, with the same layout as the matrix visual on the report page, but without any conditional formatting, icons, or other formatting. Note: If you export from a matrix visual like in this demo, you will not see any drill-down value details. You will only see the topmost hierarchy of the data, as seen in the image below. Note: The percentage formatting is also undone.

Screenshot showing exported data to Excel

If you selected the "Summarized data" option, you should have the option to export in CSV format in addition to Excel, as seen in the image below. However, this option allows you to export the summarized data originally used to create the visual only (not the raw dataset).

Screenshot showing how to export Power BI data to Excel using the Export data option 3

Note: With this option, you can export the full drill-down hierarchy with all columns showing, as seen in the image below.

Screenshot showing exported data to excel 2

If you select the "Underlying data" option, the export format will default to Excel (.xlsx) only, and the export will be based on the raw dataset, as seen in the image below.

Screenshot showing how to export Power BI data to Excel using the Export data option 3

Depending on the size of the raw dataset, this would usually take longer to export. Once exported, the Excel file will show all columns from the raw dataset used to create the report, not just the visual.

Screenshot showing exported data to excel 3

Export Power BI Report to PowerPoint

There are currently two ways to export Power BI data to PowerPoint: embed as an image or embed as live data. If you have not exported data from Power BI to PowerPoint before, you might need to put some settings in place before you can do so.

Embed a Power BI Report as an Image in PowerPoint

Click on the "Export" dropdown at the top right of the report page. Click "PowerPoint" and select "Embed an image," as seen in the image below.

Screenshot showing how to export Power BI data to PowerPoint using the Embed an image option

On the next window, click "Export," as seen in the image below.

Screenshot showing how to export Power BI data to PowerPoint using the Embed an image option 2

A download of the PowerPoint file with the pages in the report (depending on if you selected the checkbox that says "Only export current page" or not) as an image should be displayed, as seen in the image below.

Screenshot showing Power BI report embedded in PowerPoint

Embed a Power BI Report as Live Data in PowerPoint

The following steps can be used to export a live Power BI report page to PowerPoint. Microsoft documentation is available for more information.

First, follow the steps provided above to export data to PowerPoint. Then select the "Embed live data" option, as seen in the image below.

Screenshot showing how to export Power BI data to PowerPoint using the Embed live data option

After making the selection, the image below will appear. You can then choose to copy the "Report page link" or click the "Open in PowerPoint" button.

Screenshot showing how to export Power BI data to PowerPoint using the Embed live data option 2

If it's your first time using this option, you may find that once you click the "Open in PowerPoint" button, the download completes, but requires a log-in to PowerPoint first, as seen in the image below.

Troubleshooting new sign-in to Power BI embedded in PowerPoint

Similarly, you might need to activate an "Add-in" in PowerPoint to ensure Power BI exports can be viewed live. You will see this message (below) on the second page of the PowerPoint file.

Troubleshooting new sign-in to Power BI embedded in PowerPoint

To access and activate the Add-in to PowerPoint, check for "Add-ins" in the home tab on the top right of the PowerPoint page, as seen in the image below. Click the "Add-ins" section and select the "Microsoft Power BI" option by clicking "Add," as seen below.

Screenshot showing how to export Power BI data to PowerPoint using the Embed live data option 3

You can see it in your Add-ins list when clicking the Add-in section again (see image below).

Screenshot showing how to export Power BI data to PowerPoint using the Embed live data option 4

With the Add-in activated, use the "Open in PowerPoint" button option mentioned earlier to get your Power BI report embedded live in PowerPoint, as seen in the image below.

Screenshot showing a Power BI report embedded in PowerPoint live

You can also use the second option: copying the "Report page link" as seen below.

A screenshot of how to embed Power BI report to PowerPoint using the Report page link option

To do this, copy the link seen in the above image, open a new PowerPoint file, and on the "Home" tab on the top right of the PowerPoint page, select the "Add-ins" section. You will see all available Add-ins under "My Add-ins." Select the "Microsoft Power BI" Add-in. The following will appear.

A screenshot of how to embed Power BI report to PowerPoint using the Report page link option 2

You must paste the link you previously copied into the "Paste a URL from Power BI here" space. An example is shown below.

A screenshot of how to embed Power BI report to PowerPoint using the Report page link option 3

Click "Insert," which will render the embedded report live in PowerPoint.

Embed Power BI Report on a Microsoft Teams Page

This is a very popular option with Power BI end users who want to view their reports in Teams without logging into the Power BI service or moving to a different web page each time.

To embed a Power BI report or app on a Teams page, you first must consider if you currently have a Teams page or if one needs to be created. Let's assume a Teams page already exists for this demo; otherwise, see this article for directions to create one.

After accessing the Teams page, click "+" at the top and select "Power BI" from the list of apps, as seen in the image below.

A screenshot of how to embed Power BI report to Teams

A new window will appear where you can click "Save."

A screenshot of how to embed Power BI report to Teams 2

Next, the image below appears where you choose how to embed the report or app: "Paste a Power BI link" section or "Browse workspaces" options.

A screenshot of how to embed Power BI report to Teams 3

Paste a Power BI Link Option

Paste a link from your app or report in the Power BI service to use this option.

To get the link from an app, you should try to update an existing app and then copy the link. Click "Update app."

A screenshot of how to embed Power BI report to Teams using the App link

Then, copy the link by following the steps in the image below.

A screenshot of how to embed Power BI report to Teams using the App link 2

After copying the link, go back to the Teams page and paste it in the space provided, as seen in the image below.

A screenshot of how to embed Power BI report to Teams using the App link 3

You can also follow the same approach to paste the link relating to a report. To copy the link to a report, you need to open the report from within a workspace. Then copy the URL link, as seen in the image below, and paste it on the Teams page.

A screenshot of how to embed Power BI report to Teams using the Report page url link

Browse Power BI Workspaces Option

To use this option, you must first click on the hyperlink, as seen in the image below.

A screenshot of how to embed Power BI report to Teams using the Browse workspaces option

A new window will display (see below), and are given the following options:

  1. Select a report from a workspace.
  2. Select an app from your list of available apps; or
  3. Select a report shared with you.
A screenshot of how to embed Power BI report to Teams using the Browse workspaces option 2

Whichever option you select, you will see your Power BI report or app embedded on your Teams page, like in the following image. A tab is created at the top of the page for the team to click each time you need to view the report.

A screenshot of embedded Power BI report in Teams

Subscribe to a Report in Power BI Service

Many Power BI end users leverage this feature to subscribe to a Power BI report in a workspace or app and a dashboard on a schedule. It is one of the most common features Power BI end users utilize.

The feature enables Power BI users to schedule when and how reports or report pages are sent in an automated way. Users do not need to log in to the Power BI service to view reports; instead, the reports are scheduled to be sent to them periodically.

This feature is configured by clicking "Subscribe to report" at the top of the page, as seen in the image below.

Screenshot showing how to subscribe to a report in Power BI service

The process is also the same for dashboards, except the button reads "Subscribe to dashboard," as seen in the image below.

Screenshot showing how to subscribe to a dashboard in Power BI service

It is easy and self-explanatory to configure the subscription. The sections to be completed are relatively the same for both reports, apps, and dashboards. The one main difference is the ability to select a chosen report page on the app and reports subscription but not in dashboards, as one would expect (since dashboards only have a single page by default).

The image below shows the sections that must be completed to configure a report subscription. You can also expand "More options" to add additional settings. Read more about how to configure the subscription settings in this article.

Screenshot showing how to subscribe to a report or dashboard in Power BI service

In summary, this article showcases some common tips to assist Power BI end users gain more from using Power BI to enable them to use already-created reports to the fullest extent in the most efficient way possible.

It is also worth noting that other Power BI features are available that do not require technical know-how; however, I have narrowed these features to five for this article. Additional use cases might include embedding Power BI reports in SharePoint, viewing and understanding usage metrics in Power BI service, creating quick self-service reports in Power BI service, and so on. To learn more about these use cases, see the articles provided in Next Steps.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelorís and masterís degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

View all my tips


Article Last Updated: 2023-10-30

Comments For This Article