D | E | |
1 | Parent_ID | Children |
2 | 6235002350 | 8606530653 |
3 | 6235035581 | 7560863015 |
4 | 6235039872 | 7034039872 |
5 | 6235050502 | 8086700669, 8086700669, 8592931619, 8592931619, 9745271965, 9745271965 |
6 | 6235050550 | 9946164159, 9946164159 |
7 | 6235058555 | 8593851355 |
8 | 6235059900 | 9846202247, 9846202247 |
9 | 6235060606 | 7025015744, 7025653034, 7592993355, 8086225362, 8086555055, 8086600050, 8589901111, 8592871049, 8593857878, 8593877878, 8593887878, 9048661666 |
=IF(A2="","",TEXTJOIN(",",TRUE,A2,B2,FILTER(B3:B$27,ROW(A3:A$27)<MIN(IF(A3:A$27<>"",ROW(A3:A$27))),"")))
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FilledDown = Table.FillDown(Source,{"Parent_ID"}),
GroupedRows = Table.Group(FilledDown, {"Parent_ID"}, {{"grp", each _, type table [Parent_ID=number, Child_ID=number]}},GroupKind.Local),
InvokeCustomFunction = Table.AddColumn(GroupedRows, "Result", each List.Combine({{Table.FirstValue([grp])},[grp][Child_ID]})),
ExtractValues = Table.TransformColumns(InvokeCustomFunction, {"Result", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
Result = Table.SelectColumns(ExtractValues,{"Result"})
in
Result
1. Bring table into Power Query and fill down all nulls
2. Close and Load to table and make sure Data Model is checked
3. Open Power Pivot and create Pivot Table from Table
4. Create measure as follows Children:=CONCATENATEX(Table1,Table1[Child_ID],", ") using DAX
5. Pivot Table Parent ID in Rows, Children(new measure) in Values
Data Range
D E 1 Parent_ID Children 2 6235002350 8606530653 3 6235035581 7560863015 4 6235039872 7034039872 5 6235050502 8086700669, 8086700669, 8592931619, 8592931619, 9745271965, 9745271965 6 6235050550 9946164159, 9946164159 7 6235058555 8593851355 8 6235059900 9846202247, 9846202247 9 6235060606 7025015744, 7025653034, 7592993355, 8086225362, 8086555055, 8086600050, 8589901111, 8592871049, 8593857878, 8593877878, 8593887878, 9048661666
There's a not very good formula, in C2, copied down:Code:=IF(A2="","",TEXTJOIN(",",TRUE,A2,B2,FILTER(B3:B$27,ROW(A3:A$27)<MIN(IF(A3:A$27<>"",ROW(A3:A$27))),"")))
You realise you've got duplicates in column A? Two each of 6235050502, 6235050550 & 6235059900.
while applying this formula there is error shown in "filter"
Filter is Office 365 function, if your formula return error that mean you don't have Office 365.
Here is another option, which can suit with all Excel versions.
Since as your replied, there is no duplicate in Column A, data in range A23:A27 changed to 6235059901, 6235050503, 6235050551
A) "Parent ID" Column
1] In "Parent_ID" column fill all blank cells with value from above
2] Masking the duplicate value in A2:A27 with white front
- Select A2:A27
- Press F5 or Ctrl+G
- Click Special...
- Select Blanks, then click OK
- Enter the formula =A5 and press Ctrl+Enter
B) "Result" Column
- Select A2:A27
- Click Conditional Format
- Select New Rule
- In the rule b enter :=COUNTIF($A$1:$A2,$A2)>1
- and choose Font in white color
3] In C2, formula copied down :
=IF(COUNTIF($A$2:$A2,A2)=1,A2&", ","")&B2&IFERROR(", "&VLOOKUP(A2,A3:C$28,3,),)
4] Masking the C2:C7 with correspondence duplicate in A2:A27 with white front
View attachment 74547
- Select C2:C27
- Click Conditional Format
- Select New Rule
- In the rule b enter :=COUNTIF($A$1:$A2,$A2)>1
- and choose Font in white color