I need a way to collapse a table onto itself

[EaglesTony]

I have this table:

Item    Name

1         Orange

1         Apple

2         Apple

I need to collapse it onto itself, so it comes out

Item    Name

1         Orange, Apple

2         Apple

Thanks

[Anonymous]

Hi,Jai-Rathinavel ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@EaglesTony . I am glad to help you.
Jai-Rathinavel is spot on with his suggestions, here is my test of what he suggests, hope it helps.

This is my test data:

You only need to add one step
Grouping by Item, where the logic is Text.Combine

=Table.Group(#"Changed Type", {"Item"}, {{"Count", each Text.Combine([Name],", ")}})

 this is my M code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_11_25.xlsx"), null, true),
    TableTest_Sheet = Source{[Item="TableTest",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(TableTest_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item", Int64.Type}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {{"Count", each Text.Combine([Name],", ")}})
    
in
    #"Grouped Rows"

Item	Name
1	Orange
1	Apple
2	Banana
3	Orange
3	Cherry
4	Banana
4	Mango
4	Apple
5	Mango

If you find my suggestion helpful, you can mark Jai-Rathinavel's suggestion as a solution, which will help more users.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[EaglesTony]

I cant due to firewall rules.

Basically, based off this, assume table name is (Fruit):

Item    Name

1         Orange

1         Apple

2         Apple

I need to collapse it onto itself, so it comes out

Item    Name

1         Orange, Apple

2         Apple

I need steps on what column to group by, etc ?

[Jai-Rathinavel · Accepted solution]

@EaglesTony  You have to Group By Item column like below, then add a custom column using below expression.

= Text.Combine(Table.Column([Custom], "Name"), ", ")

Did I answer your question? If yes, please mark my post as a solution. 

Thanks,

Jai

[EaglesTony]

I don't need the Source, as I have these in a table called IssueComponents.

Within this table do I need to group by 1 or more columns ?

[Jai-Rathinavel]

@EaglesTony , Can you send me your power query table view so that I can answer precisely ? Thanks

[Jai-Rathinavel]

@EaglesTony , I have achieved it using a calculated column and field parameter.

Please check the attached pbix file Download PBIX

Expand

Collapse

Did I answer your question? If yes, please mark my post as a solution.

Thanks,

Jai

[EaglesTony]

Can you post the logic here instead of the download ?

[Jai-Rathinavel]

@EaglesTony here you go,

1. Create a calculated column using this dax

Collapse = 
CALCULATE(
    CONCATENATEX('Table','Table'[Name],", "),
                ALLEXCEPT('Table','Table'[Item])
    )

2. Create a calculated table as a field parameter using below dax

Switch = {
    ("Expand", NAMEOF('Table'[Name]), 0),
    ("Collapse", NAMEOF('Table'[Collapse]), 1)
}

2. Pull in the field parameter both in Slicer and the visual

3. Make the slicer to Single select

Did I answer your question? If yes, please mark my post as a solution.

Thanks,

Jai

[EaglesTony]

There would be no slicer, just the table where it has the 2 rows (1 with both Orange and Apple and the other with just Apple).

Also, any way to do this in a Power M Query, as I eventually have to merge this to another table as a seperate column ?

I just need this at the end:

Item    Name

1         Orange, Apple

2         Apple

[Jai-Rathinavel]

@EaglesTony , Please copy paste the below m query 

let
    Source = Table.FromRows(
        {
            {1, "Orange"},
            {1, "Apple"},
            {2, "Apple"}
        },
        {"Item", "Name"}
    ),
    #"Grouped Rows" = Table.Group(Source, {"Item"}, {{"Custom", each _, type table [Item=nullable number, Name=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Name", each Text.Combine(Table.Column([Custom], "Name"), ", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Item", type text}, {"Name", type text}})
in
    #"Changed Type"

 Input:

Output:

Did I answer your question? If yes, please mark my post as a solution.

Thanks,

Jai

[EaglesTony]

So it is using a Group by ?

[Jai-Rathinavel]

@EaglesTony yes, the end result is what as expected !