Merging tables from a database - Difference between "Value" and "Table"?

[Anonymous]

Good Morning, Afternoon, Evening, fellow PBI users,

I want to merge several tables from our company database, to recreate a view (or snapshot) from our ERP platform. 

This snapshot has columns from different tables which are linked together in the database. 

We are talking about employees name, contract type, etc.

In order to do that, I go to the Power Query Editor, click on Merge Query as New and select the first two tables I want to merge together with the columns on which to merge.

The result of this merge is all the columns from my initial table, together with a ton of other columns containing either yellow "Value" or "Table" links into their cells.

I understand that PBI suggests as well all the linked tables, so not only the columns from the table I want to merge, but it's a real mess. I only want the columns from my left table and the ones from my right table.

Plus, I don't understand the difference between "Value" and "Table (see below).

Weirdly enough, I haven't seen any tutorials or articles dealing with database merges in PBI. So I am asking my questions here.

Thanks in advance for your help on that!

Cheers

Antoine

[v-jingzhang · Accepted solution]

Hi @Anonymous 

When you connect to the database at the first time, expand Advanced options and uncheck Include relationship columns option. By default, this option is checked and it will import linked tables into Power BI. That's probably why you see so many unexpcted linked tables.  

For Merge Queries tutorials, you could refer to below links:

Merge queries overview | Microsoft Docs

Append vs. Merge in Power BI and Power Query - RADACAD

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hi v-jingzhang,

Thanks a lot for your reply!
I just tried your solution and it seems to work fine. The merge operation output is less messy now, as it doesn't show tens of unwanted columns from tables I didn't intentionnally load.
However, doing this means I lose the relationships between the tables I imported as well.
Isn't there a midway solution where I could still have these relationships (which are helpful as a reference to know the keys when merging) but not having relationships with other tables I didn't load?

Many thanks

Antoine

[v-jingzhang]

Hi @Anonymous 

Navigate to File > Options and settings > Options from Power BI Desktop, and select Data Load in the left pane. Check Import relationships from data sources on first load option. This will import existing relationships between imported tables from data source. 

If it doesn't import relationships on first load, you can still create a relationship manually or with autodetect in Power BI Desktop. It's very flexible. 

Reference:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thanks for your swift reply!

I already had this option ticked/activated.

So it seems that it's an all or nothing situation (whether have all the relationships even unwanted ones or no relationships at all).

The advanced option "Include relationship columns" from the connexion pane seems to overwrite the "Import relationships from data source on first load" from the options pane.

If there is something I am missing here, I am still interested in knowing the solution.

If this is indeed how PowerBI works, it may need to be improved.

Thanks anyway for your support!
If I am missing someting

[v-jingzhang]

Hi @Anonymous 

I didn't notice this before. Do the tables you want to import have relationships (foreign keys) between each other directly, not through other unimported tables?

Jing

[Anonymous]

Yes, they all have relationships between each other directly!

[KNP]

If you could share your code from Power Query (Advanced Editor) that would be helpful in diagnosing your issue.

Be sure to mask any sensitive info.

I'm specifically looking for which columns you selected to merge (join) on.

What happens if you select the split arrows at the top of one of those columns with all the tables and choose expand to new rows?

[Anonymous]

Thanks for taking the time to reply KNP, and sorry for my late reply too.

v-jingzhang's recent reply above seems to solve my problem.