Power Bi create comma seperated list string where ids match

[merijndk]

Im really new to Power Bi, I created the following table:

ID | POST_ID |
0  | 11      |
0  | 12      |
0  | 13      |
0  | 18      |
0  | 21      |
1  | 14      |
1  | 15      |
2  | 16      |
2  | 17      |
2  | 19      |
2  | 20      |

Now I need to pass this ids to an api as a comma seperated list, so I want to transform the table to:

ID | POST_ID        |
0  | 11,12,13,18,21 |
1  | 14,15          |
2  | 16,17,19,20    |

But can't manage to do this. I assume it must be fairly easy to do? I have no clue where to start, I've been messing around in the query editor now for a few hours and googling wont bring me much help either so far!

Thanks in advance!

[lalthan · Accepted solution]

Assuming your existing table name is "Concat" and the new table to be created  is "Grouped", In data view, click on new table and paste the following:

Grouped = calculatetable(
addcolumns(
summarize(
ConCat
,ConCat[ID ]
)
,"Post IDs",calculate(CONCATENATEX(ConCat,[ POST_ID ],","))
)
)

[Eric_Zhang]

---

@merijndk wrote:

Im really new to Power Bi, I created the following table:

ID | POST_ID |
0  | 11      |
0  | 12      |
0  | 13      |
0  | 18      |
0  | 21      |
1  | 14      |
1  | 15      |
2  | 16      |
2  | 17      |
2  | 19      |
2  | 20      |

Now I need to pass this ids to an api as a comma seperated list, so I want to transform the table to:

ID | POST_ID        |
0  | 11,12,13,18,21 |
1  | 14,15          |
2  | 16,17,19,20    |

But can't manage to do this. I assume it must be fairly easy to do? I have no clue where to start, I've been messing around in the query editor now for a few hours and googling wont bring me much help either so far!

Thanks in advance!

---

@merijndk

You can just create a measure. It would vary according to the slicer.

concatPostID = CONCATENATEX(YourTable,YourTable[POST_ID],",")

[Anonymous · Accepted solution]

Hi 

  You can create few columns and can hide them in the report.

Replace Table1 with your table name.

Rnk = RANKX ( FILTER ( All ( Table1 ), Table1[ID] = EARLIER ( Table1[ID] ) ), Table1[Post_ID], , 1, DENSE )

ParRnk = IF (Table1[Rnk] <> 1, Table1[Rnk]-1)

ParPost= CALCULATE ( FIRSTNONBLANK ( Table1[Post_ID], 1 ), FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[Rnk] = EARLIER ( Table1[ParRnk] ) ) )

Concat = SUBSTITUTE(CALCULATE ( PATH ( Table1[Post_ID], Table1[ParPost] ), CALCULATETABLE ( FILTER ( Table1, Table1[Rnk] = Max ( Table1[Rnk] ) ), ALLEXCEPT ( Table1, Table1[ID] ) ) ) ,"|",",")

 

Thanks

Hari

[lalthan · Accepted solution]

Assuming your existing table name is "Concat" and the new table to be created  is "Grouped", In data view, click on new table and paste the following:

Grouped = calculatetable(
addcolumns(
summarize(
ConCat
,ConCat[ID ]
)
,"Post IDs",calculate(CONCATENATEX(ConCat,[ POST_ID ],","))
)
)