Issue with missing complex columns based on data source

[Casingena]

Hi everyone,

I have been struggling with an issue regarding a data source. I have 4 tables that are spread across 4 sheets in an Excel workbook. There are relationships between the tables as shown below:

While using Power Query Editor in Microsoft Excel I can see the complex columns that allow me to expand to columns from other related tables and copy them into the current table. The complex column can be seen below with the row values Table.

My issue is that when I import the tables from Excel into Power BI Desktop, then access Power Query Editor in Power BI Desktop, these complex columns are absent as shown below:

I did some testing and converted the tables into an Access database and performed the same steps. When the tables are in an Access data source, the Power Query Editor does show the complex columns. Is there something that I have missed when setting up my tables in Microsoft Excel? I am finding it difficult to research this specific problem and I am wondering if anyone knows why Excel shows complex columns in its own Power Query Editor, but not Power BI Desktop's Power Query Editor?

[amitchandak]

@Casingena , In the second(screenshot), click on the navigation step and check.(The last step change type is clicked)

In power Query in import mode, You should be able to same operation on any source. It is like power BI Data after import.

[Casingena · Accepted solution]

@amitchandak, thanks for your quick response. With the navigation step selected I am still unable to see the complex columns:

I have just discovered that I have been importing the tables using Get Data > Excel. If I go to the File menu and choose Import > Power Query, the complex columns do appear, however I receive other issues. I think I can work on these seperately, but I think I understand that Get Data > File simply grabs the tables and Import > Power Query adopts the data model from Excel.

[amitchandak]

@Casingena , Power Query can get an excel table. How is access column is created