Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jomikk
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
TomMartens
Super User
Super User

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
TomMartens
Super User
Super User

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

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?

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

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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors