Forum Discussion

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...

Book2.xlsx16 KB

excel

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)

Lorenzo

Silver Contributor

Jun 24, 2022

Hi Excel

With your data formatted as table named Table1

Power Query code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Job", type number}, {"Part Number", type text}}),
    MergedColumns = Table.CombineColumns(ChangedType, {"Part Number", "Description"},
        Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None), "PartDescription"
    ),
    CombinedPartsAsLines = Table.Group(MergedColumns, {"Job"},
        {"Parts", each Text.Combine([PartDescription], "#(lf)"), type text}
    )
in
    CombinedPartsAsLines

PQ_Zan_Hanifee.xlsx25 KB

Excel
Iron Contributor
Jun 24, 2022
Sir, Can you please explain code in Power Query?
Please..
- Lorenzo
  Silver Contributor
  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)
  - Excel
    Iron Contributor
    Jun 24, 2022
    Thank you so much sir.