Why am I receiving additional columns for related tables, containing 'Table' or 'Value'?

[Anonymous]

Hi,

I have loaded a SQL database containing 10 tables. I did a straight loaded rather then edit before load. Power BI attempted the joins but I removed these and manually created the joins based on the joins detailed in the SQL database.
I then open Power Query editor to create my additional columns (i.e. something simple Col A + Col B). What I notice was that there are additional columns automatically created, holding either 'Table' or 'Value'. These values are in yellow and I have the option to split them out. See below.

Now the table in question is the 'CORRECTIONS' table which has joins to the 'LOCKED_CORRECTION' and 'SCRIPT_VALIDATION' tables only. Clearly, Power Query is detecting the joins and creating additional columns based on this.

Question 1: I have created many Power BI projects before where I have kept the Power BI guessed joins, deleted the guessed joins and created my own, or a mixture of both. Either way, I have not seen this occurring before. Is this a new feature release, or that I've somehow activated, or...?

When clicking the expand button above the LOCK_CORRECTION column I get columns of that table, plus another chance to load all the columns from the CORRECTIONS table again (the only table it joins too) - which is odd as it is the CORRECTIONS table that has these two additional columns are appearing in, hmm why would I want to see the columns of the CORRECTIONS table twice in one/same CORRECTIONS table. See below.


For the additional SCRIPT_VALIDATION column I have the choice to bring in all the columns for that table, plus all the columns from all the joining tables. See below.

Questions 2: Even though the two additional columns seem to be providing the same choice (i.e. columns for that table, and that tables joined to the host table), why am I receiving 'Table' for one column and 'Value' for the other column?  What does Table and Value actually represent?

Question 3: With the automatic option to join the tables into a single, larger, flat table - is this the desired method as opposed to using the different tables via joins?

I haven't seen this before so I'm wondering if its a new feature by default or is it something I've triggered. Please advise?
Thanks in advance.

[shweta_kurmi]

Hi, 

If you have already created the connect and imported the tables, you can disable the option by clicking in Source Settings --> uncheck the Include relationship columns --> OK  

For a new connection, while entering the server details click on Advanced options and uncheck the Include relationship columns --> OK 

Cheers,

Shweta

[v-piga-msft]

Hi @Anonymous,

---

 What I notice was that there are additional columns automatically created, holding either 'Table' or 'Value'. These values are in yellow and I have the option to split them out. See below.

Now the table in question is the 'CORRECTIONS' table which has joins to the 'LOCKED_CORRECTION' and 'SCRIPT_VALIDATION' tables only. Clearly, Power Query is detecting the joins and creating additional columns based on this.

Question 1: I have created many Power BI projects before where I have kept the Power BI guessed joins, deleted the guessed joins and created my own, or a mixture of both. Either way, I have not seen this occurring before. Is this a new feature release, or that I've somehow activated, or...?

Questions 2: Even though the two additional columns seem to be providing the same choice (i.e. columns for that table, and that tables joined to the host table), why am I receiving 'Table' for one column and 'Value' for the other column?  What does Table and Value actually represent?

Question 3: With the automatic option to join the tables into a single, larger, flat table - is this the desired method as opposed to using the different tables via joins?

---

This isn't the new feature of Power BI but an extended value. It may be automatically created according to the tables in your SQL database. If the two tables have a one to many relationship, it may create the extend value in Power Query.

You could click the Table and Value to see the details.

Normally, the relationship will be imported to Power BI when you load data if you have setted the option under Options and Settings. You could also change the relationships manually.

Best Regards,

Cherry