Create Table with all the possible group combinations

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.