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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ogend
Helper I
Helper I

Filtering nested table before expanding dynamically for each record of the mastertable

Hello Power Query Gurus, 

 

I have MasterTable 

Attribite1Attribite2NumberTable
applec1Table
bananad22Table
applee12Table
plumf35Table

 

Table in Column Table looks like that: 

AttributeAttribute4Attribute5
applegreen345
tomatored453
apricotorange3456
plumpurple7457

 

Is there  a smart way to filter nested Table's Attribute field for each record of the Mastertable's Attribute1 field and bring Attribute4 and Attribute5 columns to MasterTable where Attribute1= Attribute without explicitly merging the queries?

I know that it would be possible to filter nested tables by a static hardcoded value before expanding, but my scenario is more complicated as i need to do the filtering dynamicly for each record in the Mastertable. 

Not sure if this matters, but it would always be  one to many relationship between Table and MasterTable 

This is the expected output:

Attribite1Attribite2NumberAttribute4Attribute5
applec1green345
bananan22  
applee12green345
plumf35purple7457

The reason I am trying to avoid merging - I have more columns from MasterTable to match against Attribute field of the nested Table and I would want to avoid muliple merging steps. I would also ideally want to avoid transposing the MasterTable - it has about 30 columns over 500,000 rows; and the nested table only has 3 columns and about 20 rows

 

Thank you!

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

NewStep=Table.ReplaceValue(PreviousStepName,each [Attribute1],"",(x,y,z)=>Table.SelectRows(x,each [Attribute1]=y),{"Table"})

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

NewStep=Table.ReplaceValue(PreviousStepName,each [Attribute1],"",(x,y,z)=>Table.SelectRows(x,each [Attribute1]=y),{"Table"})

Thank you, that's exactly what I needed!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors