Groupby in many to many relationship

[dbonilla0331]

Hi all,

Our system create groupIDS (unique and incremental) to group activity between customerIDs. So I can have multiple groupids containing multiple customers.
Using powerquery, preferable, I would like to list all the customers that have a relationship and stack them in a new index/group.
Ex. My Table

GroupID	Customer
1	200
1	201
2	200
2	203
3	200
3	400
4	500
4	510
4	520

Output

Index	Customer
1	200
1	201
1	200
1	203
1	200
1	400
2	500
2	510
2	520

 

[alansidman]

I may be little dense, but I cannot figure out the relationships you are showing between the Input and Output. Perhaps you could explain in more detail what the criteria are to arrive at the shown output.

 

[dbonilla0331]

I may be little dense, but I cannot figure out the relationships you are showing between the Input and Output. Perhaps you could explain in more detail what the criteria are to arrive at the shown output.

the output will stack up all CustomerIDs that have shared a relationship with GroupID. So for example, groupid 1, had customers 200 and 201, then groupID 2, had 200 and 203, I need to group then in a 3rd group and stack up them together, Index 1 = 200, 201 and 203.

 

[alansidman]

Why is 400 in Index 1? Doesn't seem to be part of the 200's. Am I missing something here?

 

[dbonilla0331]

Why is 400 in Index 1? Doesn't seem to be part of the 200's. Am I missing something here?

because the 400 appears on the GroupID 3, where it stack up with the Cust Id 200.

 

[alansidman]

Sorry, I don't see a means to the end. Good Luck.

 

[JGordon11]

maybe this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"GroupID"}, {{"All", each _, type table [GroupID=number, Customer=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows(#"Grouped Rows", (x)=> [All][Customer]{0} = x[All][Customer]{0} )),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"GroupID", "All"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Index", "Custom", {"All"}, {"Custom.All"}),
    #"Expanded Custom.All" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.All", {"Customer"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom.All",{"Index", "Customer"})
in
    #"Reordered Columns"