SOLVED

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

Iron Contributor

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 :

Screenshot (683).png

 

 

And Result should show like this :

Screenshot (684).png

 

Please help..

 

Please give me screenshot of PROCEDURE.

 

Here is a attached file.

 

15 Replies

Hi @Excel 

 

With your data formatted as table named Table1

_Screenshot.png

 

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

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

best response confirmed by Excel (Iron Contributor)
Solution

@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

_Screenshot.png

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 

 

In the attached file, 2 options to do the same thing with the UI only

Now, i am very crystal clear understand in Power Query.
Thank you so much sir.

@Excel 

As variant that could be PivotTable using measure

Parts:=CONCATENATEX( Table1, Table1[Part Number] & " - " & Table1[Description]
, "
"  )

@Sergei Baklan 

Hello Sir,
I have tried many times, but not works.
Here is a screenshot :

 

Screenshot (690).pngScreenshot (691).png

 

It come in a row

 

Here is a attached file :

 

 

@L z. 

 

Hello Sir, 

I followed the same step in Power Query, but after load to sheet, then it come like this :

Screenshot (692).png

 

Please help..

 

Here is a attached file:

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

Hi @Excel 

Not quite sure this community exists to answer such a question...

You have to Wrap text in that column:

 

_Screenshot.png

- Select column [Parts]

- Click on the icon I highlighted (on the Home tab in the Alignment section)

Yes sir, Wrap text is work for me.
Thank you so much sir.
Thank you for giving the solution.
Wrap text is work for me.
Thank you so much sir.
1 best response

Accepted Solutions
best response confirmed by Excel (Iron Contributor)
Solution

@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

_Screenshot.png

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)

View solution in original post