Convert list to csv based on other column

[Anonymous]

Hi there!

I have a scenario to convert list to csv grouped by ID column, please help!

Input:

ID   Name

1       A

1       B

1       C

2       D

3       E

3       F

Output:

ID   Name

1      A,B,C

2      D

3      E,F

[Anonymous]

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 😀

[Ashish_Mathur]

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.

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

Try this:

Convert = CALCULATE(CONCATENATEX('Table','Table'[Name],","),ALLEXCEPT('Table','Table'[ID]))

Insert your table name for 'Table'. 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[lc_finance]

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