Merge Queries with multiple lookups

[ChrisDiener]

Hello.

I am wanting to create a merge query between two tables.

The column to lookup has one or multiple values which I want to return all values from the other table. e.g. I have on one row a column titled "Equipment" and the line has Equipment numbers 123, 527, 991.

I want to convert these equipment numbers to their correct name as defined on the other table.

How can this be done to create a new column using the merge query function to return Excavator, Truck, Aeroplane?

 

[ChrisDiener · Accepted solution]

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.

[Fowmy · Accepted solution]

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

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result very clearly.

[ChrisDiener]

Thank you Ashish.

[AllisonKennedy]

@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

[ChrisDiener · Accepted solution]

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.