Create a new table that contains rows of specific values only

[Anonymous]

Hi all!!

I feel this shouldn't bee to difficult of a taks but couldn't find a straight forward answer:

Table1:

Key	attribute1	attribute2	...
a	...	...	...
b	b0_a1	b0_a2	...
c	c0_a1	c0_a2	...
a	...	...	...
a	...	...	...
c	c1_a1	c1_a2	...

Table2:

Key

b

c

I wowuld like to obtain Table3 such that it shows Table1 but only for the Keys of Table2:

Table3:

Key	attribute1	attribute2	...
b	b0_a1	b0_a2	...
c	c0_a1	c0_a2
c	c1_a1	c1_a2

What is a simple solution for this?

Many thanks 🙂

[Anonymous]

Great, thanks so much!!

[v-xinruzhu-msft]

Hi, @Anonymous  

You can create a new table and input the following code, the result is displayed in the picture

Table 2 = FILTER(Table1,Table1[Key]=RELATED('Table'[key]))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Xinru Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

FreemanZ has given a good solution.

However, if you just want to import Table3 (which is what you asked for) and not all the records on Table1 and Table2 to save space then try this ...

in PowerQuery Edit Table2

Merger Queries > Merge as New

Merge with Table1 base on Key with Join Kind = Inner(all from first, matching from second)

untick the box and click ok

Expand the Table

Right click an table 1 and turn off Enable load

Right click an table 2 and turn off Enable load

Raname the merge table to Table3

Well done!  Only Tabel2 will be imported

Thanks again to FreemanZ  for their very quick response and option

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[FreemanZ]

It is indeed not difficult, do this:

1) relate two tables on the Key column

2) create a visual with Table1[Key], Table2[attribute1], and Table2[attribute2],

3) You might find a blank row, click filter, click Key, uncheck (Blank)

[Anonymous]

Ah but I would like to have a new data table, not just a visual. Such that I can do manipulations with it....

I actually found another suitable solution for my application by adding a calculated column to table1, using table2 as lookupvalues and then give each row in table1 a marker as "contained in table 2" / "not contained in table2".

However I would still be interested how I can create a new data table?

Thanks!!

[FreemanZ · Accepted solution]

Aha, then try to

1) Ensure to relate two tables on the Key column,

2) Create a table with the code below:

Table3 =
CALCULATETABLE(
    TABLE1,
    TABLE2
)

It worked like this:

It looks easy but hides some complexity. In the code, we are actually using Table2 to filter Table1. We could do this as Table2 is on the one side of a one-to-many relationship with Table1.