Combining Columns from Multiple Tables Into a Single Table

[Q630]

Hello, I am new to PowerBI and have a question about combining tables.

I have multiple queries that all pull different numbers and types of columns. There are some columns that contain the same type of data, but each query represents it differently because the tables they are pulling from represent it differently. For example, in Query 1 the value "A" in the "Product" column may be referring to the same product as "Product_A" in the "Name" column of Query 2.

I then add columns to these queries through the Data view and "New Column".  I use a Lookupvalue formula to take the non-standardized data from the original queries and translate it to standardized data based on a lookup table. The green columns in the image below are the Lookupvalue columns that I added. For example, the "Official product Name" column in the modified tables takes the different product names from each of the original queries and returns the standardized name for that product.

 

I then want to create a new table that pulls in certain columns from each of the 3 modified tables. It will contain a combination of the Lookupvalue fields that were not part of the original queries and fields that were part of the original queries. I am envisioning it would look something like the below. The data that the 3 original queries are pulling will continue to grow over time and I would like the Combined Table to be updated.

Any ideas on how to do this? Thanks!

[Analystmate]

@Q630  You can achieve desired using the "Append Query as New" feature in Power query.

Sharing below steps.

Load all table in power Query > Select "Append Query as New" form the home tab.

If tables more two select "Three or more table " > Select all required tables from the Available table list then add.

Result 

[Q630]

Hi @Analystmate, thanks for your reply. I tried your proposal and realized it would not work because some of the fields I want to combine are not part of the original queries. I updated my original question with more details to explain that.