cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Create a new table by dividing some columns from two tables

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Create a new table by dividing some columns from two tables

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Create a new table by dividing some columns from two tables

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Highlighted
Helper I
Helper I

Re: Create a new table by dividing some columns from two tables

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?

Highlighted
Super User II
Super User II

Re: Create a new table by dividing some columns from two tables

Sure, referencing is sufficient.


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Helper I
Helper I

Re: Create a new table by dividing some columns from two tables

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

Highlighted
Super User II
Super User II

Re: Create a new table by dividing some columns from two tables

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

PQ - Referencing a Query - 01.png

 

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

rename the new queryrename the new query

Regards

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors