Forum Discussion
Helper IConcatenate multiple columns to a single column
Hi Kay_Kalu ,
This is a pretty common scenario when dealing with dynamic column structures from tools like Jira. Since the number of comment columns can change over time, Power Query is probably your best bet here — it's more flexible for this kind of transformation.
Here’s a general approach you can try in Power Query:
- Load your CSV into Power Query.
- Select the columns that contain the comments (you can use a pattern like "Column1", "Column2", etc. if they follow a naming convention).
- Use the Unpivot Columns feature — this will turn your multiple comment columns into rows.
- Then you can group by the task ID or whatever identifier you have, and optionally add line breaks between comments if needed.
This way, even if the number of comment columns changes next week, the unpivot step will still work as long as you select all comment columns dynamically.
Let me know if you need help with the exact steps or M code!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.Hi Kay_Kalu
Unpivot is the way to go here, in power query, select your 'issue key' column then select 'unpivot other columns'
Remove the attribute column as this will just contain the 'comment' column headers.
Also, remove any blanks from the value column.
Next, select 'Group By' in the transform ribbon, group by the 'issue key column and select the operation 'All rows'
This will give you a nested table column for your comments.
Now add a custom column as follows
Text.Combine([Comments][Value], "#(lf)")This will result in
Remove the 'comments' column and rename 'custom' to 'comments'
I hope this helps! Please give a thumbs up and mark as solved if it does, thanks!
Hi Kay_Kalu,
I hope you had a chance to review the solution shared by wardy912 burakkaragoz . If it addressed your question, Consider accepting it as the solution it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.Thank you.
Super UserHi Kay_Kalu
Unpivot is the way to go here, in power query, select your 'issue key' column then select 'unpivot other columns'
Remove the attribute column as this will just contain the 'comment' column headers.
Also, remove any blanks from the value column.
Next, select 'Group By' in the transform ribbon, group by the 'issue key column and select the operation 'All rows'
This will give you a nested table column for your comments.
Now add a custom column as follows
Text.Combine([Comments][Value], "#(lf)")
This will result in
Remove the 'comments' column and rename 'custom' to 'comments'
I hope this helps! Please give a thumbs up and mark as solved if it does, thanks!