Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Power Query Gurus,
I have MasterTable
Attribite1 | Attribite2 | Number | Table |
apple | c | 1 | Table |
banana | d | 22 | Table |
apple | e | 12 | Table |
plum | f | 35 | Table |
Table in Column Table looks like that:
Attribute | Attribute4 | Attribute5 |
apple | green | 345 |
tomato | red | 453 |
apricot | orange | 3456 |
plum | purple | 7457 |
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:
Attribite1 | Attribite2 | Number | Attribute4 | Attribute5 |
apple | c | 1 | green | 345 |
banana | n | 22 | ||
apple | e | 12 | green | 345 |
plum | f | 35 | purple | 7457 |
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!
Solved! Go to Solution.
NewStep=Table.ReplaceValue(PreviousStepName,each [Attribute1],"",(x,y,z)=>Table.SelectRows(x,each [Attribute1]=y),{"Table"})
NewStep=Table.ReplaceValue(PreviousStepName,each [Attribute1],"",(x,y,z)=>Table.SelectRows(x,each [Attribute1]=y),{"Table"})
Thank you, that's exactly what I needed!