Hello,

I am new to the use of power query.

​

Sorry if the post is long but I want to give as much details as possible.

This is my general case, I have a table with similar values for different products, the products are subject to different type of test.

Each product table is on separate sheets.

Here is what I have done:

1.I created a query with all the tables and added a column with the product

2.I used power query and appended all the tables in a new query I call Full_Results

What I want know is to add two additional columns to the Full_Results:

One column should come from the domain table and show the domain type (power, response time, security, etc.) according to the value of the Workload column.

The second column should come from Pass_table and show the Max diff according to the test of the Workload column.

I tried to merge Full_Results and domain table, or full results with pass table but no luck.

I also tried to create a relationship between Full_Results query and the domaintable, or Pass_table but does not work

I also though on the possibility of creating the Full_Results as a table and then add the other columns manually but I need the data of Full_Results to update if there is any change on the original tables.

I also though on creating a relationship between the workload fields of all the tables, but they are 28 at the moment with a likelihood to increment so do it it manually is not feasible and I don't know how to do it any other way.

My end goal is to have a Pivot Table with the following columns

​

Product, Domain, Workload, Base, Target, Diff, Pass Criteria

​

​

https://preview.redd.it/3aiqpjo4w8ha1.png?width=462&format=png&auto=webp&v=enabled&s=0fae030c7cfa83bb0e85b5e51c52a7d41167bf4b

​

Thanks I hope you can help me with this.