Create Table with all the possible group combinations

[kta87]

Hi Everyone,

  Is there any function in power query that can create a table with all the possibile group combinations?

 I have one table (Table1) with Customer ID, DOC ID and value, and I've managed with multiple merge to generate a new table (Table 2) with all the unique combinations and the sum of the values. But since the number of DOC ID's per customer is variable, I need to do additional merge in order to have all the combination.

Also I want to create the third table having the Unique ID combinations and the containing ID's.

Thank you in Advance,

Kta

[Jakinta]

By using great code from this post  by @AlexisOlson , this is one solution that should be reworked, tweaked and turned into a function.

1st requirement

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLzdPYHUsamSrE60UpGCBEjAwOwkDFCyAQqZIIQ0jUFCsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DOC ID" = _t, #"Customer ID" = _t, Value = _t]),
    Type = Table.TransformColumnTypes(Source,{{"DOC ID", Int64.Type}, {"Customer ID", type text}, {"Value", Int64.Type}}),
    L=Type[DOC ID],
    L2= Type[Value],
    N = List.Count(L),
    Subsets = 
        List.Transform(
            {0..Number.Power(2, N)-1},
            (i) => List.Transform(
                        {0..N-1},
                        (j) => if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1
                                then L{j}
                                else null
                    )
        ),
    Custom1 = List.Select(List.Transform(Subsets, each let l=List.RemoveNulls(_), t= List.Transform(l, Text.From)  in Text.Combine(t)), each _ <> ""),
    Subsets2 =
        List.Transform(
            {0..Number.Power(2, N)-1},
            (i) => List.Transform(
                        {0..N-1},
                        (j) => if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1
                                then L2{j}
                                else null
                    )
        ),
    Custom2 = List.RemoveNulls ( List.Transform(Subsets2, each let l=List.RemoveNulls(_) in List.Sum(l))),
    ID = List.Repeat({Type[Customer ID]{0}}, List.Count(Custom2)),
    Table = Table.FromColumns ( {ID, Custom1, Custom2}, {"Customer ID", "Unique ID Combinations", "SUM Value"}),
    #"Changed Type" = Table.TransformColumnTypes(Table,{{"Unique ID Combinations", Int64.Type}, {"SUM Value", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Unique ID Combinations", Order.Ascending}})
in
    #"Sorted Rows"

2nd requirement 

I referenced the table above in the Source step

let
    Source = Table,
    Type = Table.TransformColumnTypes(Source,{{"Unique ID Combinations", type text}}),
    RemovedSum = Table.RemoveColumns(Type,{"SUM Value"}),
    InsertedDOCID = Table.AddColumn(RemovedSum, "DOC ID", each Text.ToList([Unique ID Combinations])),
    ExpandedDOCID = Table.ExpandListColumn(InsertedDOCID, "DOC ID")
in
    ExpandedDOCID

[nickvanmaele]

I have done some more research, and the most elegant method is described on Microsoft Learn on this page. 

The above URL works with an example of two tables having only one column each. My example below is showing how to do this using two tables with multiple columns each. 

If you have two tables: 

• Table A with columns "ID", "Person Name"
• Table B with columns "Task ID", "Task Name"

The set of all possible combinations of two lists or two columns is called the "Cartesian product". 

If you want a table with rows that contain all possible combinations of "Person Name" and "Task Name", then do this: 

1. In Power Query, ensure that you create queries referring to Table A and Table B. Let's call these queries "pqA" and "pqB" respectively. They show the tables identically as they are without any modifications. 
2. Create a new query by referencing "pqA". Call the new query "pqCartesianProduct". It will initially look identical to "pqA". 
3. In query "pqCartesianProduct", add a new Custom Column.
4. In the Custom column formula field, type "= pqB[Task Name]". 
   Note: the auto-suggest feature will not help you here. It will try to "correct" you but suggest wrong things. Just go ahead and type the query name and the correct column name in square brackets. Pay attention to case sensitivity.
5. After clicking done, each row in column "Custom" of query "pqCartesianProduct" will now show "List". 
6. Expand the Custom column, and choose the option to create new rows. 
7. Done. 

[nickvanmaele]

This might do the trick: 

https://exceloffthegrid.com/list-of-all-possible-combinations-using-power-query/

[MAwwad]

Yes, there is a function in Power Query that can create a table with all possible combinations. The function is called "Cartesian Join" or "Cross Join".

To create a table with all possible combinations of the Customer ID and DOC ID from your Table1, you can follow these steps:

1. Select your Table1 and click on the "Transform data" button.

2. In the Power Query Editor, click on the "Add Column" tab and select "Custom Column".

3. In the "Custom Column" dialog box, enter a name for the new column (e.g. "JoinKey") and enter the following formula: [Customer ID] & "-" & [DOC ID]

4. Click "OK" to create the new column.

5. Duplicate the Table1 by right-clicking on the Table1 in the Queries pane and selecting "Duplicate".

6. Rename the duplicated table as "Table2".

7. Remove the "Value" column from Table2.

8. Select both Table1 and Table2 in the Queries pane.

9. Click on the "Combine" button in the Home tab and select "Merge Queries".

10. In the "Merge" dialog box, select "JoinKey" as the join column for both tables.

11. Select "Cross Join" as the join kind.

12. Click "OK" to create the new table with all possible combinations.

To create the third table with the Unique ID combinations and the containing IDs, you can follow these steps:

1. Select your Table2 and click on the "Transform data" button.

2. In the Power Query Editor, click on the "Group By" button in the Home tab.

3. In the "Group By" dialog box, select "JoinKey" as the grouping column and select "All Rows" as the aggregation method.

4. Rename the new column as "DOC IDs".

5. Click "OK" to create the new table with unique JoinKeys and the containing DOC IDs.

[kta87]

Hello,

Thanks for the response, but unfortunatelly I'm not having the expected result.

After the merge I got this table, which is not ok.

Cheers,

Kta