Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jun 24, 2022
Solved

Question related to combine a column with duplicated numbers into one cell containing several rows

Hello Everyone,   I would like to combine all duplicated Job numbers into one cell, but have the part numbers and description relevant to the job number in a single cell next to it. Is this possible...
excel
  • Lorenzo's avatar
    Lorenzo
    Jun 24, 2022

    Excel 

     

    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)

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 25, 2022

Excel 

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.

Excel's avatar
Excel
Iron Contributor
Jun 25, 2022
Yes sir, Wrap text is work for me.
Thank you so much sir.

Resources