Merge table

If you want to merge them, you could do that in the Power Query Editor so that they all sit in 1 flat table. I'm assuming you want the Note 1.1 next to Status 1.1 for Item 1? 

You could try this with Relationships by relating Table 1 and Table 2 each to the original column using that first column row number as Key, or you can do this in Power Query using merge: 

Since you have already done the separating and unpivoting, now you need to piece them back together. If you don't have that first column (doesn't have a name in your screenshot but I'll call it "Index") in Table1 and Table2, get it back in them. 

Then in the ADD COLUMN tab, extract the 1.2, 1.3, etc part from the Attribute columns in both tables Table1 and Table2. Call this new column AttNo

Finally, in the ADD COLUMN tab, merge the Index and AttNo columns in both Table 1 and Table 2. Call this merged column MergeKey

Now you're ready to merge the tables. In the Home tab, Merge Queries as New. Select the MergeKey from Table1 and Table2. Select the Join Kind (I think you might want Full Outer here). 

Finally, expand the Table2 column to select the Attribute and Value. If you used Full Outer, you'll want to select a lot of the other columns as well, and then will need to Merge them with the Table1 values. Easiest way to do this without custom M code is to create a new conditional column which says IF Table1[Name] is null then Table2[Name] else Table1[Name]. Then Remove the Table1 and Table 2 Names and keep only the newly merged Name. 

A bit hard to explain all that without images, so let me know if you have any questions. 

Has this post solved your problem? Please mark it as a 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.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv