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  | 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!

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

3 Replies

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

     

  • lalthan's avatar
    lalthan
    Icon for Resolver II rankResolver II
    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
    Not applicable
    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