MS365 - Power Query - combine rows (error message)

[geo0041]

Hi everyone, this is my first post on here, so hopefully will adhere to forum rules !

I have attached a sample table that i am trying to combine rows - i have also manually inserted a "Desired output" showing how i would like the finished table to look.

I have used Power Query to try and do this and have only been partially successful - I have error messages in relation to trying to combine the following cells - A2 + A3, B5 + B6, C8 + C9. (In Power Query I have "Transformed and unpivotted the columns, selected the 1st cell in "attribute" column and pivotted the column, i have selected values and in advanced options, selected aggregate value function and selected "don't aggregate", then finally "close + load")


Not really sure what i need to do as a next step to resolve this - help appreciated !

[alansidman]

In the attached.

1. Unpivot the data
2. Close and Load to a Spreadsheet
3. Create Pivot Table and Add to Model, ie. Power Pivot
4. Create Measure using DAX formula Concatenatex

Look at this link to understand the Concatenatex

https://sfmagazine.com/post-entry/ju...a-pivot-table/

[geo0041]

Hi Alan, thanks for the quick response - I will give that a go and let you know how it works.

George

ps Hope all is good in Steamboat Springs - stay safe !

[CK76]

Alternate approach using PowerQuery only.

After unpivot columns operation....
- Group Rows by Unique ID & Attribute. New Column [Temp] with All Rows.
- Add custom column [Concat]

Please Login or Register  to view this content.

-Remove [Temp]
-Pivot column with Attribute selected. Value column as [Concat]
- Reorder column as needed.

Please Login or Register  to view this content.

[geo0041]

Hi CK76, I decided to go with your proposed solution as I was already using Power Query, delighted to say that it has solved the problem for me !
Thanks again for your help. George

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.