Import Excel Workbooks in Power BI Dashboards

Yes, you heard it right, you can import Microsoft Excel Workbook to Power BI and show you selected section in you powered dashboards, let’s see how it works, as Microsoft Excel is one of the most widely used business applications around. It’s also one of the most common ways to get your data into Power BI.

Requirement:

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.

Raw Data Vs Range Tables:

If your workbook has simple worksheets with ranges of data, to get the most out of your data in Power BI, be sure to format those ranges as tables. This way, when creating reports in Power BI, you’ll see named tables and columns in the Fields pane, making it much easier to visualize your data.

Power View Sheets, Pivottables And Charts:
How your PowerView sheets and PivotTables and charts appear, or not appear, in Power BI depends on where your workbook file is saved and how you choose to get it into Power BI. We’ll go into this more below.

Data Types:
Power BI supports the following data types: Whole Number, Decimal Number, Currency, Date, True/False, Text. Marking data as specific data types in Excel will improve the Power BI experience.

Saving you Excel file local or OneDrive can make a difference:

Local – If you save your workbook file to a local drive on your computer or another location in your organization, from Power BI you can load your file into Power BI. Your file will remain on your local drive, so the whole file isn’t really imported into Power BI. What really happens is a new dataset is created in Power BI and data and the data model (if any) from the workbook are loaded into the dataset. If your workbook has any Power View sheets, those will appear in your Power BI site under Reports. Excel 2016 also has the Publish feature (under the File menu) which is discussed in our other blog.

OneDrive – Business – If you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, this is by-far the most effective way to keep your work in Excel and your dataset, reports, and dashboards in Power BI in-sync. Because both Power BI and OneDrive are in the cloud, Power BI connects to your workbook file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI.

Two Ways to Import Excel workbook:
1. OneDrive
2. Import Excel data into Power BI

Import or connect to an Excel workbook from Power BI
1. In Power BI, in the navigation pane, click Get Data.

2. In Files, click Get.

3. Find your file.

4. If your workbook file is on OneDrive or SharePoint – Team Sites, choose Import or Connect.

Open the workbook and select he section that you want to pin to Dashboards.

Try this yourself, it’s an amazing feature and allows us to use the standalone data points to show in Dashboards when even required. Feel free to contact us in case of any issue.