Forum Discussion
Question related to combine a column with duplicated numbers into one cell containing several rows
Well...
Until the MergedColumn step (included) this can be done with the UI so I suspect you're interested by step CombinedPartsAsLines that - in the way I did it - can't be done with the UI only
1/ Group the table by column [Job], Operation = All rows
2/ Go to the Advanced Editor. The Group Rows step says:
=Table.Group(MergedColumns, {"Job"}, {{"Parts", each _, type table [Job=nullable number, PartDescription=text]}})3/ Replace it with:
=Table.Group(MergedColumns, {"Job"}, {{"Parts", each Text.Combine([PartDescription], "#(lf)"), type text}})4/ Close the Advanced Editor (keep your changes)
What #3 does: take all values of column [PartDescription] and contenate them with a LineFeed char.
( the , type text at the end isn't mandatory)
1) In this measure
Parts:=IF(HASONEVALUE(Table1[Job]) ,CONCATENATEX( Table1, Table1[Part Number] & " - " & Table1[Description] , "
" ), "" )
between two quotes for CONCATENATEX delimiter enter Alt+Enter in formula bar.
2) If you use totals, I added IF( HASONEVALUE(... not to show result in totals
3) Select cells in column there are Parts in PivotTable, Ctrl+1 and set Wrap text, otherwise text will be in one line.
Please check in attached.
Thank you so much sir.
Excel , you are welcome