Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JoostttP
Regular Visitor

Microsoft Access database nested tables

Hi all,

 

I have been working with power BI for a week now. I do have experience with Qlik Sense and the advanced modeling of Qlik Sense.

 

When I import a table from demo database from Access into Power BI it sometimes shows "nested" tables which are not present in the original source. I am using a Northwind demo file.

 

Unfortunatly I have not been able to find extensive information as to how Access databases are being treated. Could somebody explain as to why, when and what the benefits are of this feature. In addition, is it possible to turn this feature of?

As far as I can see it makes merging different tables easier.

 

In the attached screenshot this phenomenon shows in the last colomns, those with either "Value" or "Table" in yellow. I am aware that I can click through and add the information by using the arrows in the header.

 

http://imgur.com/a/fiW0e

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

Hi @JoostttP

Based on my observation, when loading tables from Access database to Power BI Desktop, Power BI Desktop imports the columns in these tables as well as Object Dependencies of these tables into Query Editor, and Power BI treat the dependencies as new columns of these tables.

For example, for order table in Access database, there are other 8 objects depending on it, Power BI Desktop puts the 8 objects as additional columns in order table. From my point of perspective, this behavior is proper, Power BI Desktop loads all properties of a table(data and dependencies), it doesn’t ignore dependencies and it makes us easy to select values from related tables. 
1.PNG2.PNG

Moreover, if you don’t want to retain these additional columns, you can directly remove these columns in Query Editor of Power BI Desktop or delete the object dependencies of your tables in Access database.
3.PNG



Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

Hi @JoostttP

Based on my observation, when loading tables from Access database to Power BI Desktop, Power BI Desktop imports the columns in these tables as well as Object Dependencies of these tables into Query Editor, and Power BI treat the dependencies as new columns of these tables.

For example, for order table in Access database, there are other 8 objects depending on it, Power BI Desktop puts the 8 objects as additional columns in order table. From my point of perspective, this behavior is proper, Power BI Desktop loads all properties of a table(data and dependencies), it doesn’t ignore dependencies and it makes us easy to select values from related tables. 
1.PNG2.PNG

Moreover, if you don’t want to retain these additional columns, you can directly remove these columns in Query Editor of Power BI Desktop or delete the object dependencies of your tables in Access database.
3.PNG



Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft,

 

Thank you for your response. I figured it would be something like this. Thank you for your explanation.

 

What is your opinion on either merging the additional data this way or importing both tables first, and then create a merge based on a shared ID? What would be faster is perfomance you recon?

 

Thanks,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.