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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Create a new table that contains rows of specific values only

Hi all!!

 

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

 

Table1:

Keyattribute1attribute2...
a.........
bb0_a1b0_a2...
cc0_a1c0_a2...
a.........
a.........
cc1_a1c1_a2...

 

Table2:

Key
b
c

 

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

 

Table3:

Keyattribute1attribute2...
bb0_a1b0_a2...
cc0_a1c0_a2 
cc1_a1c1_a2 

 

What is a simple solution for this?

 

Many thanks 🙂

1 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:

FreemanZ_0-1668958343081.png

 

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. 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Great, thanks so much!!

v-xinruzhu-msft
Community Support
Community Support

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]))

 

vxinruzhumsft_0-1669009093653.png

 

 

 

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
Community Champion
Community Champion

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

speedramps_0-1668943628007.png

 

speedramps_1-1668943665229.png

 

speedramps_2-1668943814252.png

 

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

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)

 

FreemanZ_1-1668942946900.png

 

 

FreemanZ_0-1668942889196.png

 

Anonymous
Not applicable

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!!

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:

FreemanZ_0-1668958343081.png

 

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. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors