Forum Discussion

merijndk's avatar
merijndk
New Member
9 years ago
Solved

Power Bi create comma seperated list string where ids match

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 | ...
Need Help
Show and Tell
  • lalthan's avatar
    lalthan
    9 years ago

    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 ],","))
    )
    )

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago
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],",")