From the outset, Microsoft has pushed the idea of Excel and Power BI being ‘better together’, but in the early days it was not easy to share your Excel data so that you could perform analysis on it. Once the data was there, it was easy enough to work with, but getting the data into PowerBI so that you could use the Analyze tool was a challenge.

That changed with the release of PowerBI Publisher for Excel – a simple tool that does exactly what it says on the tin (or in this case, on the ribbon).

PowerBI Publisher for Excel takes away a lot of the administrative and technical burden. You could use the OLE drivers and convert the data the old-fashioned way, but there’s no need. You don’t even need to set up all the pivot tables by hand. PowerBI Publisher handles it all for you, saving you time and reducing the possibility of errors creeping in.

Using the Publisher Tool

You can find the publisher tool in the PowerBI ribbon in Excel itself. To share data from a spreadsheet, open that spreadsheet in Excel, select the PowerBI ribbon, and click on Connect to Data. This will bring up a dialog that will give you the option to connect to a dataset or to a report.

If you don’t have any data in PowerBI yet, then the tool will offer to create a sample dataset for you, and connect your data to that dataset. This is a good way to get started. If you approve this, then the tool will make a dataset and a pivot table, and deploy the data to the dataset.

From there, you can use the Analyze tool to perform the analysis that you want on the data. To ensure that you can access the reports and analysis quickly and easily in the future, once you have finished the process be sure to pin the results.

Common Issues With Sharing Excel Data

One thing that it’s important to understand when working with Excel data is that if you have data which is shared with you, but that is not owned by you, then that data may not connect to PowerBI in the way you expect. If you want to share data with PowerBI then the data needs to be directly a part of the spreadsheet so that you have full control over it.

In addition, if the data in PowerBI comes from an ‘on premises’ SQL database, the connection that is made by the tool will be between the Excel sheet and that database, rather than the PowerBI version of the data. This means that you will need to make sure that you are properly authenticated to connect to that dataset.

If your setup is quite simple, however, sharing excel data with PowerBI is quite simply a matter of clicking a few buttons. The wizard takes control of the rest of the process for you, and in most cases it will work without a hitch.