Forum Discussion
Merge Queries with multiple lookups
Thank you Allison.
I am testing the option of Split Column by Delimiterm using each comma as an occurrence and split into rows.
It appears that all the information contained on that row duplicates when there are multiple Equipment entries, leaving only one Equipment item per row.
ChrisDiener
On the Plant and Equipment - On Hire Query or copy of this query, right click on the Equipment column where you have the numbers seperated with comma, choose Split Column and on the dialog box, choose Rows under Advanced options, this will split the column into rows then you will be able to merge with the other table on the Equipment column:
ChrisDiener I can't think of a way to do this without altering your model. You need a 'fact' table that details each equipment on the project, in this table, each row would only have 1 equipment ID. This 'fact equipment on project' table would connect to the project table, and also to the equipment table, using one to many relationships in each case.
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html
Thank you Allison.
I am testing the option of Split Column by Delimiterm using each comma as an occurrence and split into rows.
It appears that all the information contained on that row duplicates when there are multiple Equipment entries, leaving only one Equipment item per row.