Search code examples
sqlpowerbidaxpowerquerym

How to combine rows with the same ID into a list

How can I make the below table like a list.

Id   Name
1    Tim
1    George
2    Rachael
3    Mark
3    Blake

I want the result to be like this

Id    Name
1     Tim,George
2     Rachael
3     Mark,Blake

Any ideas?

Solution

  • try the following, it may solve your problem.

    Let's say your existing table name is yourTable and the new table to be created is groupedNames. in data view, click on new table and paste the following:

    groupedNames = calculatetable
(
    addcolumns(
        summarize(yourTable ,yourTable[Id ]),
        "Names",calculate(CONCATENATEX(yourTable,[ Name ],","))
    )   
)