Jun 24 2022 05:31 AM - edited Jun 24 2022 06:12 AM
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.
Jun 24 2022 06:31 AM
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
Jun 24 2022 06:42 AM - edited Jun 24 2022 06:48 AM
Sir, Can you please explain code in Power Query?
Please..
Jun 24 2022 07:15 AM
Solution
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)
Jun 24 2022 07:18 AM
Jun 24 2022 07:30 AM
Jun 24 2022 07:35 AM
Jun 24 2022 07:48 AM
Jun 24 2022 08:28 AM
As variant that could be PivotTable using measure
Parts:=CONCATENATEX( Table1, Table1[Part Number] & " - " & Table1[Description]
, "
" )
Jun 25 2022 06:06 AM
Hello Sir,
I have tried many times, but not works.
Here is a screenshot :
It come in a row
Here is a attached file :
Jun 25 2022 06:10 AM
Hello Sir,
I followed the same step in Power Query, but after load to sheet, then it come like this :
Please help..
Here is a attached file:
Jun 25 2022 06:36 AM
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.
Jun 25 2022 10:38 AM
Hi @Excel
Not quite sure this community exists to answer such a question...
You have to Wrap text in that column:
- Select column [Parts]
- Click on the icon I highlighted (on the Home tab in the Alignment section)
Jun 25 2022 10:51 AM
Jun 25 2022 10:52 AM
Jun 24 2022 07:15 AM
Solution
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)