The purpose of this document is to demonstrate how to merge two tables, or to extract just the column(s) required from one table, making them accessible in another.


As an example, this can be useful when a record refers to a User by their 'internal ID', but it is more convenient and clear for users to have the User's name displayed instead.


Pre-requisites

  • Data imported into Power BI Desktop, via Tugger's OData links. This can be in a report, or by just using OData (as per the following example).
  • Two tables, one of which has data you wish to merge into another. Note: there can be more than two tables, but at least two are needed.
  • One table should reference the other, which is needed to identify the correct data.


Although the example here uses Harvest, the steps are the same regardless of data source, providing you have identified the 'id' used to link the tables.


This will be demonstrated during this guide.


To keep it simple and clear, I have just imported the two tables being used for the demonstration, as the image below displays (1 in the image)


Click on the Transform Data 'down arrow' (2 in the image) and select Transform Data from the options.



This will open up the Power Query Editor.


Follow the steps in the image below:


1. From the home tab.


2. Click on the dropdown, likely displaying: 'Merge Queries'.


3. Select the option: 'Merge Queries as New'.


Important: This method will create a new table with the two additional columns; if you wanted to just add the additional columns to the existing table, then select 'Merge Queries'.


There is no right and wrong here, it is purely what you need; there are instances where you may want tables with different data, although to reduce the data size it might be better to avoid this if possible.



This will open up another window, called: 'Merge'.


Here you will select the table (1) that you wish to add columns to.


The source table, is selected as (2); this has the columns we wish to add to the required table.



We need to identify how the tables are linked (3) and (4) indicate 'user_Id' and 'id'. 


Select these columns.


5. This is likely to be already correctly selected; just ensure that for 'Join Kind', the Left Outer is the selected option. An explanation of the different methods to join tables is beyond the scope of this document.


6. Click Ok, when ready.



It may appear as though nothing has happened.


1. Examine the last column in the table; your table (as the example here) may require you to scroll to the end of the table.


2. Select the little grey button in the column; this will open the dropdown indicated in the image.


3. Select the required columns; as our intention here was to add the users name, I've selected their 'first_name' and 'last_name'. Select whatever is appropriate to yourself.


4. Ok once happy.




Once complete you should see the table (either the original table you added the columns to, or the new table that has been created with the additional columns).


1. This is the action you have just completed.


2. The columns added. Note the name of them.


3. When happy, Click on Close & Apply, using the dropdown (via small down arrow) if it is not automatically visible.



Once back in the main Power BI Window, you should see your changes. If you created a new table, then as per our example, it will be displayed under the Data column, indicated; 'Merge1' in the example here.


Or...


If you added them to the existing table, you should see the same in the existing table, namely the original columns and the newly added ones.



1. Displays the new table; depending on the option you chose, it will be either a new table, or the fields will be added to the existing table.


2. These are the fields added in this example.


3. Indicates the only option we had previously, namely showing the Users ID.



This concludes the document 'Merge Columns from One Table to Another Table'.



If you require assistance, please contact us at: support@tuggerapp.com.