With Microsoft Power BI publisher for Excel, you can take snapshots of your most important insights in Excel, like PivotTables, charts, and ranges and pin them to dashboards in Power BI.
Oct 13, 2015 PowerPivot, PowerQuery, and PowerMap in Office 2016 in 365. Powerpivot, powerquery and powermap are all available in excel 2016: see: integrating power query technology in excel 2016, get started with power map. However if you are now using office. In this tutorial I’ll show you how make an awesome looking map in Excel in under 5 minutes with a free plugin called PowerMap (formerly GeoFlow). Download and install the plugin. Power Map has been renamed to Microsoft 3D Maps in Excel 2016, and is located on the Insert tab in the Excel ribbon.Get started with 3D Maps in Excel 2016. Jul 09, 2018 Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. Or visualization in tools like Power View and Power Map. Find out new BI features in Excel 2016 and get familiar with the new features easily by going thorough step-by-step explanations. Integrating BI features with Excel: Power View and Power Map: As you know, Power Pivot was integrated as a built-it feature to Excel 2013. Now I’m really happy that the same thing happened to Power View.
What can you pin? Just about anything in an Excel worksheet. You can select a range of cells from a simple sheet or table, a PivotTable or PivotChart, illustrations and images, text.
What you can't pin: you cannot pin 3D Maps or visualizations in Power View sheets. There are also some elements you can pin, but it wouldn't make much sense to, like a Slicer or Timeline filter.
When you pin an element from Excel, a new tile is added to a new or existing dashboard in Power BI. The new tile is a snapshot, so it's not dynamic but you can still update it. For example, if you make a change to a PivotTable or chart you've already pinned, the dashboard tile in Power BI isn't updated automatically, but you can still update your pinned elements by using Pin Manager. You'll learn more about Pin Manager in the following sections.
Download and install
Power BI publisher for Excel is an add-in you can download and install on desktop versions of Microsoft Excel 2007 and later.
Once you have the publisher installed, you'll see a new Power BI ribbon in Excel, where you can sign in (or sign out) of Power BI, pin elements to dashboards, and manage elements you've already pinned.
The Power BI publisher for Excel add-in is enabled by default, but if for some reason you don't see the Power BI ribbon tab in Excel, you'll need to enable it. Click File > Options > Add-ins > COM Add-ins. Select Microsoft Power BI Publisher for Excel.
Pin a range to a dashboard
You can select any range of cells from your worksheet, and pin a snapshot of that range to an existing or a new dashboard in Power BI. You can pin the same snapshot to multiple dashboards, too.
To begin, you need to make sure you're signed in to Power BI.
Select Profile from the Power BI ribbon tab in Excel. If you're already signed in to Power BI, you'll see a dialog that shows which account you're currently signed in with. If that's the account you want to use, great - go to the next set of steps to pin your range. Select Sign out if you want to use a different Power BI account. If you're not signed in, go to the next step (Step 2).
If you're not signed in, select the Sign In link that appears when you select Profile from the Power BI ribbon tab in Excel, in the Connect to Power BI dialog type in the email address of the Power BI account you want to use, then select Sign In.
Once you're signed in, follow these steps to pin a range to a dashboard:
In Excel, select the Power BI ribbon tab to see the Pin ribbon button.
Select a range from your Excel workbook.
Click the Pin button from the Power BI ribbon to show the Pin to dashboard dialog. If you're not already signed into Power BI, you'll be prompted to do so. Select a workspace from the Workspace dropdown list. If you want to pin to your own dashboard, verify My Workspace is selected. If you want to pin to a dashboard in a group workspace, select the group from the drop-down list.
Choose whether you want to pin to an existing dashboard or create a new dashboard.
Click Okay to pin your selection to the dashboard.
In Pin to dashboard, select an existing dashboard in the workspace or create a new one, and then click the Ok button.
Pin a Chart to a dashboard
Just click on the chart, and then click Pin .
Manage pinned elements
With Pin Manager, you can update (refresh) a pinned element's associated tile in Power BI. You can also remove the pin between an element you've already pinned to dashboards in Power BI.
To update tiles in your dashboard, in Pin Manager select one or more elements and then select Update.
To remove the mapping between a pinned element in Excel and the associated tile in a dashboard, remove Remove. When you select Remove, you're not removing the element from your worksheet in Excel or deleting the associated tile in the dashboard. You are removing the pin, or mapping, between them. The removed element will no longer appear in Pin Manager. If you pin the element again, it will appear as a new tile.
To remove a pinned element (a tile) from a dashboard, you'll need to do that in Power BI. In the tile you want to delete, select the Open menu icon and then select Delete tile .
Connect to data in Power BI
Beginning with the July 2016 release of Power BI publisher for Excel (including the current release, linked to above), you can connect directly to data in the Power BI service and analyze that data in Excel using PivotTables and PivotCharts. This features makes it easy to use Power BI data and Excel together to analyze data that's most important to you.
Improvements include the following:
- Any drivers required to connect to data in Power BI are automatically updated with each release - no need to install or manage those drivers yourself.
- You no longer need to download .odc files to create the connections - Power BI publisher for Excel creates the connections automatically when you select which report or dataset you want to use.
- Now you can create multiple connections and PivotTables in the same workbook
- Errors are improved and specific to Power BI publisher for Excel, rather than using default Excel messages
Power Map Excel 2016 Download Windows 7
How to connect to Power BI data in Excel
To connect to Power BI data using Power BI publisher for Excel, follow these easy steps:
Make sure you're signed in to Power BI. The steps describing how to sign in (or to sign in with a different account) are provided earlier in this article.
Once you're signed in to Power BI with the account you want to use, select Connect to Data from the Power BI ribbon tab in Excel.
Excel connects to Power BI using an HTTPS connection and presents the Connect to data in Power BI dialog, where you can select the workspace from which you want to select your data (1, in the image below), which type of data you want to connect to, either a report or a dataset (2), and a drop down (3) that allows you to select which available report or dataset to which to connect.
When you make your choices and select Connect from the Connect to data in Power BI dialog, Excel prepares a PivotTable and displays the PivotTable Fields pane, where you can select fields from your connected Power BI data, and create tables or charts that help you analyze the data.
If you don't have any data in Power BI, Excel detects that and offers to create sample data for you to connect to and try.
There are a few things to consider in this release of Power BI publisher for Excel:
- Shared data - Data that has been shared with you, but isn't directly visible to you in Power BI, is not available in Connect to Data.
- SSAS on-premises - If the dataset you select originates from an on-premises SQL Server Analysis Services (SSAS) and the dataset in Power BI uses DirectQuery to access the data, Power BI publisher for Excel connects to that data through the on-premises network connection, and does not go through Power BI to connect to that data. As such, any user trying to connect to such datasets must be connected to the on-premises network, and is authenticated for access to that data using the authentication method employed by the Analysis Services instance where the data is stored.
- Required drivers - Power BI publisher for Excel installs all the necessary drivers for this feature to work, and does so automatically. Among those automatically installed drivers is the Excel OLE DB driver for Analysis Services; if that driver is removed by the user (or for any other reason), the connection to Power BI data will not work.
- Dataset must have measures - The dataset must have model measures defined in order for Excel to treat the measures as values in PivotTables, and to correctly analyze the data. Learn more about measures.
- Support for Groups - Datasets shared with people outside the specified group are not supported, and cannot be connected to.
- Free versus Pro subscriptions - Activities associated with groups are not supported for free users of Power BI, and thus won't see any datasets or reports shared with a group in their own workspace.
- Shared reports or datasets - Reports or datasets that were shared with you cannot be connected to.
- Using Tables instead of Data models - Datasets and reports that are created by importing only tables from Excel (without a data model) are not supported at this time, and cannot be connected to.
Once you've created compelling charts or other visuals such as a range of data, you can easily pin those to a dashboard in Power BI, as described earlier in this article.
There are many ways to use Excel and Power BI together, and get the best out of both. Take a look at the following articles for more information.-->
With Excel 2016, you can publish your Excel workbooks right to your Power BI site, where you can create highly interactive reports and dashboards based on your workbook’s data. You can then share your insights with others in your organization.
Before we go any further, there are few things to keep in mind:
- Before you can publish to Power BI, your workbook must be saved to OneDrive for Business.
- The account you use to sign in to Office, OneDrive for Business, and Power BI must be the same account.
- You cannot publish an empty workbook or a workbook that doesn’t have any Power BI supported content.
- You cannot publish encrypted or password protected workbooks, or workbooks with Information Protection Management.
- Publishing to Power BI requires modern authentication be enabled (default). If disabled, the Publish option is not available from the File menu.
To publish your Excel workbook
In Excel, select File > Publish.
Local file publishing
Starting in the February 2017 update, Excel 2016 supports publishing of local Excel files. They do not need to be saved to OneDrive for Business or SharePoint Online.
Only Excel 2016 with an Office 365 subscription will see the experience to publish with local files. Excel 2016 standalone installation will still have the 'Publish' only behavior which requires the excel workbook be saved to OneDrive for Business or SharePoint Online.
When you select Publish, you will be able to select the workspace you want to publish to. This can be your personal or group workspace that you have access to.
you’ll get two options on how to get your workbook into Power BI.
Once published, it is kept as a copy in Power BI, separate from the local file. If you want to update the file in Power BI, you need to publish the updated version again. You can refresh the data and set scheduled refresh on the workbook or the dataset in Power BI.
Publishing from Excel Standalone
If your workbook isn’t already saved to OneDrive, you’ll need to save it there first. Select Save to Cloud and choose a location in OneDrive for Business.
Once your workbook is saved to OneDrive, when you select Publish, you’ll get two options on how to get your workbook into Power BI.
Upload your workbook to Power BI
When you choose this option, your workbook will appear in Power BI just like it would in Excel Online. But, unlike Excel Online, you’ll have some great features to help you pin elements from your worksheets to dashboards.
You can’t edit your workbook in when open in Power BI, but if you need to make some changes, you can select Edit, and then choose to edit your workbook in Excel Online or open it in Excel on your computer. Any changes you make are saved to the workbook on OneDrive.
When you upload, no dataset is created in Power BI. Your workbook will appear in Reports, in your workspace navigation pane. Workbooks uploaded to Power BI have a special Excel icon, identifying them as Excel workbooks that have been uploaded.
Choose this option if you only have data in worksheets, or you have PivotTables and Charts you want to see in Power BI.Using Upload from Publish to Power BI in Excel is pretty much the same as using Get Data > File > OneDrive for Business > Connect, Manage and View Excel in Power BI from Power BI in your browser.
Export workbook data to Power BI
When you choose this option, any supported data in tables and/or a data model are exported into a new dataset in Power BI. If you have any Power View sheets, those will be re-created in Power BI as reports.
You can continue editing your workbook. When your changes are saved, they’ll be synchronized with the dataset in Power BI, usually within about an hour. If you need more immediate gratification, you can just select Publish again, and your changes are exported right then and there. Any visualizations you have in reports and dashboards will be updated, too.
Choose this option if you’ve used Get & Transform data or Power Pivot to load data into a data model, or if your workbook has Power View sheets with visualizations you want to see in Power BI.
Using Export from Publish to Power BI in Excel is pretty much the same as using Get Data > File > OneDrive for Business > Export Excel data into Power BI from Power BI in your browser.
When you choose either option, Excel will sign in to Power BI with your current account, and then publish your workbook to your Power BI site. Keep an eye on the status bar in Excel. It shows how things are going.
When complete, you can go to Power BI right from Excel.
Excel data in Power BI
More questions? Try the Power BI Community