Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Power BI Data Import and Export with Excel


By:   |   Read Comments   |   Related Tips: More > Power BI

Problem

How can I import my Excel workbooks into Power BI Online?  Are there any options to export data from Power BI to a CSV file?  If so, can you provide the steps to complete both tasks?

Solution

The Excel functionality in Power BI is not strictly limited to just the Analyze in Excel option which was outlined in the previous tip Analyzing Power BI data with Excel. Of course, if we have a local table we can easily import that data into Power BI. Power BI expands upon this functionality with the ability to import various Excel workbooks and worksheets covering local Power BI datasets and visualizations and also simply exporting your dataset to a CSV file.

In order to get started, we need to first verify we have Power Query and Power Pivot installed in Excel. If you have Excel 2010 or Excel 2013, you can download Power Query. For Office 2016 Power Query is "integrated" into the Data tab on the ribbon. Concerning Power Pivot for Office 2013 & Office 2016, the add-in can be enabled by going to File > Options > Add-Ins. For Office 2010, it is a little more tricky; you will need to download and install the Add-In. Instructions for completing that install for Excel 2010 can be found here

Import Data to Power BI Online

Our first steps for this tip will go over some of details of getting regular Excel data into Power BI online. The process is remarkably easy, but there are some definite caveats and cautions as you work with the data. You can actually connect/import to Excel data all the way back to Excel 2007, but hopefully you will have a much newer version! 

There are actually two methods of importing your Excel spreadsheet into Power BI online. The first method is to publish the worksheet to Power BI online via the Publish option. This method, as shown below, is convenient especially if you are already in Excel and you just want to quickly place the workbook into Power BI. However, this option is only available with Excel 2016 and only if you have access to save your Excel file to OneDrive for Business.

Publish Data to Power BI

Using this method, after you upload the workbook to OneDrive for Business, you are offered two alternative methods for saving the data:

  • Option 1 - Uploads the workbook directly to Power BI.
  • Option 2 - Exports the workbook data to Power BI as a dataset.

 

Publish to Power BI Options

The alternative method to getting your Excel file onto Power BI online is to use the Get Data option on the Power BI site. I actually prefer this option as it seems more intuitive to me and has less restrictions (i.e. it lets me load my Excel 2013 files which we are still in use at many organizations).

Get Data button in Power BI

Get Data - Import or Connect to Data including files and databases

After clicking the Get button, shown in the above screen print, we are presented with a set of options, as illustrated below. At this point it would probably be a good idea to discuss these different options and the advantages and disadvantages of each. It would also be good to point out that when importing into Power BI online, we are essentially creating a new dataset on Power BI and not necessarily loading the entire Excel Workbooks. 

  • Local files - The Excel file in this case will continue to reside on your "on premise" local machine or file share. Only the dataset (and some local Power BI datasets and reports... more on that item later) is uploaded to the Power BI site.  
  • OneDrive for Business -  This option actually loads the workbook into your Business OneDrive account, creates any related datasets and reports on Power BI online, and then creates a direct "sync link" between the online workbook and the datasets and reports. The "sync link" then updates the data about every hour.
  • OneDrive for Personal Individuals - This option is essentially the same as OneDrive for Business except you will be logging into OneDrive with your personal account.
  • SharePoint - This option also works similar to the OneDrive for Business option.
As you can see you really do have an array of choices on where to save the files.

However a few limitations do exist with the file upload process:
  • File size is currently limited to 250MB.
  • Only xlsx/xlsm files can be uploaded.
  • Power BI prefers the data to be in a clean table format. If the data is not in orderly rows and columns, Power BI likely will not be able to import the data or will import an empty dataset.
Get Data File options including Local File, OneDrive - Business, OneDriver - Personal and SharePoint - Team Sites.

Selecting the Local File method presents us with even more decision points, as illustrated next. We can either just import data into Power BI or we can upload the entire workbook into Power BI. Using the first option keeps the file local which allows for easy editing on your desktop. However this option also requires that you refresh the data either via a manual refresh or by setting up a scheduled refresh.

Get Local File Option 1

You can only schedule an automated refresh if the workbook contains a Power BI data model or if the data table is linked to a data source.

Schedule an Automated Refresh in Power BI

Now would also be a good time to emphasize the importance of using good naming conventions in your Excel workbooks. The name assigned to an Excel table will be the name that is used in the Power BI dataset; likewise the column names from the table become the field names in the dataset.

Specify the table name in Excel to reference the Power BI data

At this point the dataset can be used to create a report in Power BI.


Create a report in Power BI with the data set

Now moving on to the second upload method, Upload your Excel file to Power BI, this functionality actually imports the entire workbook into Power BI. As you see in the below illustration, the workbook is placed in the Reports Area of the Power BI menu. Furthermore, notice the Excel thumbnail appears next to the name of the file.

Upload Excel Data to Power BI

You will also notice that the file allows you certain edit and filter functionality, quite similar to what you would be able to do with the online version of Excel.

Power BI permits data editing and filtering similar to Excel

You also have the ability to select cells or an object within your uploaded workbook and Pin it to an existing or new dashboard.

Pin data from an Excel Workbook in Power BI

Pin to New Dashboard in Power BI

As you can see below, the cell range selected was pinned to new dashboard called FinSample1.

Pinned Range of data in Power BI

Up to this point, we have been working with a simple Excel workbook with a single table. What if we exploit the Power Query and Power Map functionality in Excel by adding a Sales Model Power Query dataset and then building a Power Map visual, all within Excel?

Sales Model to use for a Power Map visual in Power BI

Power Map Visual in Excel

Using the Import option, the dataset is loaded into Power BI online and can be used to create new reports.

Data set in Power BI to create new reports

 However, we also see that the Power BI automatically added our PowerMap to the Reports section of Power BI online. From here we can edit the map (report) by adding items such as a filter or we can pin it to a dashboard. That functionality is very convenient.

Power Map Plus Reports options

 

Export Data from Power BI

Up to now, we have covered importing from Excel, but you can also export data from Power BI. You can use the Analyze in Excel option as mentioned at the beginning of this tip. However, sometimes you would just want a flat data file in CSV format. To initiate this export, you simply click on the ellipse button (3 dots in the upper right corner) of any visualization. Next you can click on the Export data button / link.


Export data from Power BI to CSV

A CSV file will then be generated pertaining to the data presented in the visual. You should note that if the visualization shows summary data, the data exported to the CSV file will be the summary data. For the above chart example, the data is rolled up by month, so the export file below shows the two data points summarized by month.

Exported CSV data viewed in Excel

Conclusion

Within this tip we considered several methods of taking data which resides in Excel, and importing it into Power BI online. Several choices must be made when completing the import. First, where the source Excel file will reside; these locations include local files, One Drive for Business, One Drive - Personal, and SharePoint. Next, you must decide whether the data should be imported or if the Excel file as a whole should be uploaded.  The first option brings the data in as a dataset while the second option creates an Online Excel like Report within Power BI. Finally, we showed how we can not only import data from Excel, but also quickly export the data which makes up a visualization into a CSV file.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools