Convert list to csv based on other column

Hi,

This M code in the Query Editor works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All Names", each Text.Combine(List.Distinct([Name]), ", "), type text}}),
    Joined = Table.Join(Source, "ID", #"Grouped Rows", "ID"),
    #"Removed Columns" = Table.RemoveColumns(Joined,{"Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.

Hi Anonymous ,

You can obtain that by creating a calculated table and using CONCATENATEX.

The Power BI file with the proposed solution is available from here.

Below is the DAX formula for the calculated table (you can create a calculated table by going to Modeling -> New Table)

Output = ADDCOLUMNS(
    VALUES('Input'[ID])
    , "Name", 
    VAR currentID = [ID]
    RETURN CONCATENATEX(
        FILTER('Input', 'Input'[ID]=currentID)
        , [Name],",")
)

This formula does the following: first, it creates a row for each distinct ID value. So you will have one row for ID 1, one row for ID 2, and one row for ID 3. Then, for each row, it finds all the names in your initial table.

For example, for row ID 1, it will find three names: A, B, and C.

Finally, it concatenates these names and separates them with a comma.

Here is a screenshot:

Hope this helps you! Do not hesitate if you have further questions.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

Option 4: https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values

Option 5: GroupedRows = Table.Group(Source, {"ID"}, {"Name", each Text.Combine(_[Name], ",")})

Thanks guys! so many options to choose from 😀