Create table getting data from table hosted in another query

[baironaguillera]

Hi all,

I need to create a table with 3 columns getting data from another tables that are in another queries.

I would like to use advanced editor to create this table.

Example:

Column1(Text)	Column2(Number)	Column3(Number)
Test	Get data from a measure COUNT from ('Query1'[Table1])	get data from a row from (Query2'[Table2])

Please, help me!!!

[v-huizhn-msft]

Hi @baironaguillera,

In Power Query Edit window, you want to create a new table including the 3 columns, right? If it is, we are unable to achieve it, we use Query Edit to edit the resource table, you can add costomer, filter resource table, append or merge tables, but can not create a whole new table.

For your requirment, you can filter a resouce table to only have Column1, in the table add two coustomer tables(deperately from 'Query1'[Table1] or Query2'[Table2]).

If you also create a new table by click "New table" under Modeling on PowerBI Desktop Home page.

If this is not what you want, please share your sample data for further analysis.

Best Regards,
Angelia

[baironaguillera]

Hi Angelia,

Follow the sample about the tables:

Table 1:

Product Name	Contracted
Product1	100
Product2	200
Product3	300
Product4	400
Product5	500

Table 2:

User	Email	Product Name	Computer	Last Logon
User1	User1@email.com	Product1	Computer1	User1
User2	User2@email.com	Product3	Computer2	User2
User3	User3@email.com	Product5	Computer3	User3
User4	User4@email.com	Product2	Computer4	User4
User5	User5@email.com	Product4	Computer5	User5
User6	User6@email.com	Product2	Computer6	User6
User7	User7@email.com	Product4	Computer7	User7
User8	User8@email.com	Product1	Computer8	User8
User9	User9@email.com	Product5	Computer9	User9
User10	User10@email.com	Product5	Computer10	User10
User11	User11@email.com	Product1	Computer11	User11
User12	User12@email.com	Product5	Computer12	User12
User13	User13@email.com	Product2	Computer13	User13
User14	User14@email.com	Product5	Computer14	User14
User15	User15@email.com	Product4	Computer15	User15
User16	User16@email.com	Product1	Computer16	User16
User17	User17@email.com	Product3	Computer17	User17
User18	User18@email.com	Product2	Computer18	User18
User19	User19@email.com	Product4	Computer19	User19
User20	User20@email.com	Product1	Computer20	User20

Table that I need:

Product Name	Contracted	In Use
Product1	100	5
Product2	200	4
Product3	300	2
Product4	400	4
Product5	500	5

I need do drill down in the last column from in this last table.

Tks, 

[dkay84_PowerBI]

Is there a relationship between these different tables?

[baironaguillera]

Hi,

I have the column Product Name that I can use to create the relationship. 

Follow the sample about the tables:

Table 1:

Product Name	Contracted
Product1	100
Product2	200
Product3	300
Product4	400
Product5	500

Table 2:

User	Email	Product Name	Computer	Last Logon
User1	User1@email.com	Product1	Computer1	User1
User2	User2@email.com	Product3	Computer2	User2
User3	User3@email.com	Product5	Computer3	User3
User4	User4@email.com	Product2	Computer4	User4
User5	User5@email.com	Product4	Computer5	User5
User6	User6@email.com	Product2	Computer6	User6
User7	User7@email.com	Product4	Computer7	User7
User8	User8@email.com	Product1	Computer8	User8
User9	User9@email.com	Product5	Computer9	User9
User10	User10@email.com	Product5	Computer10	User10
User11	User11@email.com	Product1	Computer11	User11
User12	User12@email.com	Product5	Computer12	User12
User13	User13@email.com	Product2	Computer13	User13
User14	User14@email.com	Product5	Computer14	User14
User15	User15@email.com	Product4	Computer15	User15
User16	User16@email.com	Product1	Computer16	User16
User17	User17@email.com	Product3	Computer17	User17
User18	User18@email.com	Product2	Computer18	User18
User19	User19@email.com	Product4	Computer19	User19
User20	User20@email.com	Product1	Computer20	User20

Table that I need:

Product Name	Contracted	In Use
Product1	100	5
Product2	200	4
Product3	300	2
Product4	400	4
Product5	500	5

I need do drill down in the last column from in this last table.

Tks, 

[Vvelarde · Accepted solution]

@baironaguillera

Hi, Please follow the Image:

If you need a New Merge Table Select Merge Queries as New 

[baironaguillera]

Hi @Vvelarde

In this example you merger 2 tables.

Is it possible merge another one using the same column name?

Then we will use 3 tables.

Tks!!

[v-huizhn-msft]

Hi @baironaguillera,

As I tested, we are unable to merge three table at one time. You can merge the two tables to one new table, then merge new table to third table.

Best Regards,
Angelia