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 with the help of POWER QUERY or VBA code??

like - 

This is a data :

 

 

And Result should show like this :

 

Please help..

 

Please give me screenshot of PROCEDURE.

 

Here is a attached file.

 

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)

15 Replies

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

  • Lorenzo's avatar
    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
    • Excel's avatar
      Excel
      Iron Contributor
      Jun 24, 2022

      Sir, Can you please explain code in Power Query?
      Please..

      • Lorenzo's avatar
        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)

Resources