Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
@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:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
Share data in a format that can be pasted in an MS Excel file and show the expected result very clearly.
Thank you Ashish.
@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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |