Power BI Blog: Fully Imported

Welcome back to this week’s edition of the Power BI blog series. This week, I look at how to import data from Excel.

Consider the following example:

I have been busy, importing lots of tables, transforming the data and creating PivotTables. In Power Pivot, I have created multiple measures and built up a substantial Data Model, which can be viewed in Diagram View:

Now, however, I have decided I want to upload this model to Power BI. The good news is, this is easy to do. Whilst there is not a Data Model in Power BI, it is possible to load all the tables, relationships and measures. The reverse is not true, as I will show next time, so when I am creating a model, if I think I may need to have it in Excel, then it is best to create the relationships and measures in Excel and then import it to Power BI

There are several places in Power BI that seem to import from Excel, but they work differently. When I open the Power BI Desktop app, it appears to helpfully provide me with the ideal way to load my Excel model:

This takes me to a browse window where I can select my file:

However, when I select my file, the results are not ideal:

This looks very much like the Power Query engine. This means I would have all my tables and fields, but no relationships. This is not what I want here.

Another tempting choice is the ‘Excel Workbook’ icon on the Home tab:

This too, will allow me to browse and pick an Excel file, but…

Yes, I am back to the same option of just loading the tables. The ‘Get Data’ button next to ‘Excel Workbook’ will also take me to this screen for an ‘Excel Workbook’. There is another way…

The answer is on the File tab:

On the Import tab, there is an option to Import from ‘Power Query, Power Pivot, Power View’. The way to remember this, is that to get the relationships I need the Data Model, and that is in Power Pivot. I choose this option, and if I haven’t already saved my file I may get a warning:

Having saved my Power BI file the browse window appears:

This time, however, when I choose the Excel file, something different happens:

This is much more promising. I choose Start, and Power BI updates me as it progresses through the Data Model:

I get a message when the process is complete:

I can then view the file that has been created for me. Note this is a new Power BI file, completely separate from the file I saved a moment ago.

The tables and fields appear in the Fields pane. On the Diagram view, I can see the relationships have been transferred:

Everything I did in Power Query, Power Pivot and Excel has been successfully transferred.

Having imported everything, I can build visuals to be shared with other users via Power BI Service.

If I create all the tables, relationships and measures in Power BI, I don’t have the same option of importing it into Excel. This is not surprising, given that Power BI builds on what exists in Excel. I can analyse a Power BI dashboard from Power BI Service:

This process needs the most recent Excel Libraries:

Once this is done, I have a simple version of the Power BI model in Excel:

What I don’t have, is the actual Data Model in Power Pivot:

I can create PivotTables to analyse the data, but I don’t have a full Data Model. This is why, if I need the Data Model to exist in Excel and be available to Power BI, I must create it in Excel and then import it from Excel into Power BI.

Check back next week for more Power BI tips and tricks!