Power Bi For Excel



-->

Better protect your data across Power BI reports, dashboards, and data sets with persistent protection that keeps working even when shared outside your organization or exported to other formats such as Excel, PowerPoint, and PDF. Power BI supports importing or connecting to workbooks created in Excel 2007 and later. Workbooks must be saved as.xlsx or.xlsm file type and be under 1 GB. Some features described in this article are only available in later versions of Excel. Workbooks with ranges or tables of data.

Today, Power BI users are able to analyze this data in Excel by using a live, connected experience. We’re adding a new way to connect to certified and promoted datasets that will make it easier for all Microsoft 365 E5 users to find, connect, and analyze data from these datasets directly from Excel. Flatten your data. For Excel power users, this may come as a bit of a shock, but workbooks destined to be linked to Power BI for its data visualization tools must be restricted to flat data.

Your manager wants to see a report on your latest sales and profit figures by the end of the day. But the latest data is in files on your laptop. In the past, it’s taken hours to create a report, and you’re beginning to feel anxious.

No worries. With Power BI, you can create a stunning report and share it in Microsoft Teams in no time!

In this tutorial, we upload an Excel file, create a new report, and share it with colleagues in Microsoft Teams, all from within Power BI. You'll learn how to:

Power Bi For Excel Add In Download

  • Prepare your data in Excel.
  • Download sample data.
  • Build a report in the Power BI service.
  • Pin the report visuals to a dashboard.
  • Share a link to the dashboard.
  • Share the dashboard in Microsoft Teams

Prerequisites

  • Sign up for the Power BI service.
  • Download the Financial Sample workbook and save it your computer or to OneDrive for Business.

Prepare data in Excel

Let’s take a simple Excel file as an example.

  1. Before you can load your Excel file into Power BI, you must organize your data in a flat table. In a flat table, each column contains the same data type; for example, text, date, number, or currency. Your table should have a header row, but not any columns or rows that display totals.

  2. Next, format your data as a table. In Excel, on the Home tab, in the Styles group, select Format as Table.

  3. Select a table style to apply to your worksheet.

    Your Excel worksheet is now ready to load into Power BI.

Analyze in excel download

Upload your Excel file to the Power BI service

The Power BI service connects to many data sources, including Excel files that live on your computer.

  1. To get started, sign in to the Power BI service. If you haven’t signed up, you can do so for free.

  2. In My workspace, select New > Upload a file.

  3. Select Local File, browse to where you saved the Financial Sample Excel file, and select Open.

  4. On the Local File page, select Import.

    Now you have a Financial Sample dataset. Power BI also automatically created a blank dashboard. If you don't see the dashboard, refresh your browser.

  5. You want to create a report. Still in My workspace, select New > Report.

  6. In the Select a dataset to create a report dialog box, select your Financial Sample dataset > Create.

Build your report

The report opens in Editing view and displays the blank report canvas. On the right are the Visualizations, Filters, and Fields panes. Your Excel workbook table data appears in the Fields pane. At the top is the name of the table, financials. Under that, Power BI lists the column headings as individual fields.

You see the Sigma symbols in the Fields list? Power BI has detected that those fields are numeric. Power BI also indicates a geographic field with a globe symbol.

  1. To have more room for the report canvas, select Hide the navigation pane, and minimize the Filters pane.

  2. Now you can begin to create visualizations. Let's say your manager wants to see profit over time. In the Fields pane, drag Profit to the report canvas.

    By default, Power BI displays a column chart with one column.

  3. Drag Date to the report canvas.

    Power BI updates the column chart to show profit by date.

    December 2014 was the most profitable month.

    Tip

    If your chart values don't look as you expect, check your aggregations. For example, in the Values well, select the Profit field you just added and ensure the data is being aggregated the way you'd like it. In this example, we're using Sum.

Create a map

Your manager wants to know which countries are the most profitable. Impress your manager with a map visualization.

  1. Select a blank area on your report canvas.

  2. From the Fields pane, drag the Country field to your report canvas, then drag the Profit field to the map.

    Power BI creates a map visual with bubbles representing the relative profit of each location.

    Looks like the European countries are outperforming the North American countries.

Create a visual showing sales

What about displaying a visual showing sales by product and market segment? Easy.

  1. Select the blank canvas.

  2. In the Fields pane, select the Sales, Product, and Segment fields.

    Power BI creates a clustered column chart.

  3. Change the type of chart by choosing one of the icons in the Visualizations menu. For instance, change it to a Stacked column chart.

  4. To sort the chart, select More options (...) > Sort by.

Spruce up the visuals

Make the following changes on the Format tab in the Visualizations pane.

  1. Select the Profit by Date column chart. In the Title section, change Text size to 16 pt. Toggle Shadow to On.

  2. Select the Sales by Product and Segment stacked column chart. In the Title section, change title Text size to 16 pt. Toggle Shadow to On.

  3. Select the Profit by Country map. In the Map styles section, change Theme to Grayscale. In the Title section, change title Text size to 16 pt. Toggle Shadow to On.

Pin to a dashboard

Now you can pin all of your visuals to the blank dashboard that Power BI created by default.

  1. Hover over a visual and select Pin visual.

  2. You need to save your report before you can pin a visual to the dashboard. Give your report a name and select Save.

  3. Pin each visual to the dashboard that Power BI created, Financial Sample.xlsx.

  4. When you pin the last visual, select Go to dashboard.

  5. Power BI added a placeholder Financial Sample.xlsx tile to the dashboard automatically. Select More options (...) > Delete tile.

  6. Rearrange and resize the tiles any way you want.

The dashboard and report are ready.

Share a link to your dashboard

Now it's time to share your dashboard with your manager. You can share your dashboard and underlying report with any colleague who has a Power BI account. They can interact with your report, but can't save changes. If you allow it, they can reshare with others, or build a new report based on the underlying dataset.

  1. To share your report, at the top of the dashboard, select Share.

  2. In the Share dashboard page, enter the email addresses of the recipients in the Enter email addresses box and add a message in the box below it.

  3. Decide which of these options you want, if any:

    • Allow recipients to share your dashboard.
    • Allow recipients to build new content using the underlying datasets.
    • Send an email notification to recipients.

  4. Select Share.

Share to Microsoft Teams

You can also share reports and dashboards directly to your colleagues in Microsoft Teams.

  1. To share in Teams, at the top of the dashboard, select Chat in Teams.

  2. Power BI displays the Share to Teams dialog. Enter the name of a person, group, or channel and select Share.

  3. The link appears in the Posts for that person, group, or channel.

Next steps

  • Now that you've created a basic report in the Power BI service, how about creating a report in Power BI Desktop? Try the tutorial, From Excel workbook to stunning report in Power BI Desktop.

More questions? Try the Power BI Community.

Since its release last March, Analyze in Excel has been warmly welcomed by the Power BI community. It brought new meaning to the term better together that Power BI and Excel are working hard to achieve.

With this month’s release of Power BI publisher for Excel, we’re pleased to announce that Analyze in Excel is now easier than ever, and can be initiated directly from Excel using Power BI publisher. With this release, there’s no need to download .odc files, and no need to manually install OLE DB driver. Just install the latest version of Power BI publisher for Excel, and use Connect to Data from the Power BI ribbon tab in Excel.

Why should I use Power BI publisher to do Analyze in Excel?

With Power BI publisher for Excel you get a simple and friendly experience, which lets you achieve more with your Excel connection to Power BI:

  • No need to install the AS OLE DB drivers and keep them updated manually; with this latest release they are built in with Power BI publisher for Excel, and automatically updated with Power BI publisher’s updates
  • No need to download .odc files to create the connections and pivot tables; Power BI publisher creates the connection and the pivot table automatically. All you have to do is pick the report or the dataset, using a simple, intuitive dialog in Excel
  • Create multiple connections and pivot tables in the same workbook

That's cool! So how do I use it?

After installing the July update of Power BI publisher for Excel, open Excel and go to the Power BI ribbon tab. Click Connect to Data (you’ll need to sign in to Power BI first).

You’ll see the following dialog, which lets you select the source data to connect to in Power BI. You can connect to Power BI reports or datasets:

Power Bi For Excel 2016

After you select the workspace, and the report or dataset, just press the Connect button and that’s it! You’re ready to analyze with a pivot table connected to your data in Power BI. The connection is live, meaning that each interaction on the Pivot table will initiate a query to Power BI to get fresh data.

Note: Currently, data that was shared with you, but not directly visible to you in Power BI, is not available in the Connect to Data dialog.

Power Bi For Excel Users

SQL Server Analysis Services On-premises source: Datasets in Power BI that originate from SQL Server Analysis Services that reside inside the organization network (‘On-premises”), will be connected to the on-premises source and not through Power BI. This means that the user trying to connect must be on the on-premises network, and also authenticated on the Analysis Services in use.

What if I don’t have any data in Power BI yet?

If you’re new to Power BI and don’t have any data yet, don’t worry! We have a special treat just for you: Power BI publisher for Excel will recognize that you don’t have data, and will suggest that you import available sample data into your Power BI workspace. Once you approve, the sample data will be deployed on your Power BI workspace and a pivot table that is connected to it will be created.

This update also includes bug fixes and stability improvements.

Analyze away! And don’t forget to pin your insightsto your dashboard when you’re done

If you want to learn more about Power BI publisher for Excel, you can read our documentation.

For addiitonal education on Analyze in Excel, see here.

Download Power Bi Publisher For Excel

As always, feel free to send us your questions or feedback directly to xlpbifdbk@microsoft.com, or use one of the following channels:





Comments are closed.