Power BI Blog: Not Imported

Welcome back to this week’s edition of the Power BI blog series.  This week, we look at an issue when importing data from Excel.

Consider the following Excel data:

In Power BI, I can ‘Import data from Excel’.  

This takes me to a browser window where I can select my file, and I choose the Excel Workbook and see the data on the Navigator dialog:  

In Power BI Blog: Fully Imported, I described how I could load the whole Excel data model by accessing the File tab.  To show why this should only be used when there is a data model to be preserved, I will repeat the process for this simpler Excel workbook:  

On the Import tab, there is an option to Import from ‘Power Query, Power Pivot, Power View’.    I choose this option, and if I haven’t already saved my current Power BI file, I may get a warning:  

Having saved my Power BI file, I browse and choose my Excel Workbook:  

I choose Start, and Power BI updates me as it progresses:

This time however, I don’t get the results I was looking for:

Notice the name of the option on the Import tab:

I have no queries, so the data cannot be uploaded this way.  Clearly the best way to import this data, is to use the ‘Import from Excel’ option I started with, which can also be accessed from the ‘Get Data’ dropdown.  The Import ‘Power Query, Power Pivot, Power View’ option is not designed to cope with this.  Let’s see what happens if I persist with this method…

If I extract all my data to queries in the Excel Workbook using ‘From Table/Range’ on the ‘Get & Transform’ section of the Data tab:

I can then ‘Close and Load To’ from the Power Query Editor:

I don’t need two copies of the data, so I only create a connection:

Having done this for all the data I want to import, I can save and close the Excel Workbook and try again, and this time I get an extra step:

I can opt to ‘Keep connection’:

I am then prompted to ‘Apply changes’:

The Fields pane doesn’t look promising!

If I choose to ‘Transform data’ from the Home tab, the data is in the Power Query editor:

Everything is in italics in the Queries pane, so I can ‘Enable Load’ for Table5 by right-clicking:

It then appears in the Fields pane:

Now, what if I had used ‘Copy data’ instead:

The tables with the information option have not been copied as they are too large, the connection has been kept instead.   The results are actually worse this way; not only is everything still set not to load, but some of the data hasn’t been copied in the correct format:

Only use the Import ‘Power Query, Power Pivot, Power View’ option to import data from Excel when there is a data model and relationships to preserve.  Power Query really is the best tool for importing Excel data to Power BI in all other circumstances!

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