Create a new table by dividing some columns from two tables

[jomikk]

Hi, I have two tables with 7 columns, the first four columns are text and common for both. And then I have 3 columns, for 2015, 2016, 2017. I want to create a third table with the first four common columns, and then the 3 last columns by dividing Table A-2015 with Table B-2015, and same for 2016 and 2017.

I can't change the query to get the desired result, so I have to manipulate the tables like this.

Is it possible to do this in the Query Editor or do I have to use DAX? Suggestions are welcome!

Best regards

[TomMartens · Accepted solution]

Hey,

not sure if I totally understand your requirement, but maybe this will work.

In the Query Editor mark one table and choose "Merge Queries", this allows you to pull columns from the second table into the first table. Be aware, that the Merge Operation does not create a third table. If you really need three tables, copy the queri of one table first.

You have to select column in both tables (the keys), these table identify the same rows, whereas it is not necessary that there are same number of rows in each table identified by the key columns.

In a second step you can expand the newly content and just select the missing columns.

Now you can create custom columns where you divide the columns accordingly.

Hope this helps 

[jomikk]

Thanks! It works, but I see in the Advanced Editor that it runs the query of the original table behind the scenes. As I've already got a separate query for the other table, I think it is unnecessary to run the query twice. Is it not possible to only reference to the tables?

[TomMartens]

Sure, referencing is sufficient.

[jomikk]

How do I do that? 🙂 Not exactly a power user here, hehe.

[TomMartens]

Hey, no problem.

In the Query Editor mark the query you want to reference in the queries pane and choose "Reference" from the context menu

After that you have 2 queries, you should rername the new query properly 🙂

rename the new query

Regards