Sequencing repeating cells - to create a unique ref for merging queries

Hi PenPadCalc,

You don't have to combine field to generate a key. Within the Merge dialog hold down CTRL and select "Batch","Nominal","Supplier","Date" fields, they will be assigned an index number in the order in which they were selected. Repeat that process for the table to merge and make sure the index/order of selected key fields is the same.

The result, a nested table with none, one or multiple rows... And you could combine contract ID's to avoid duplication. Create a Custom Column and enter the formula below in the formula area. Replace [YourMergeColumnName] with the name of the column that contains the nested table from the available columns section on the right hand side.

Text.Combine( [YourMergeColumnName][Contract], ", ")

I hope this is helplful.

To create a unique reference for merging your two tables while dealing with duplicate "BNSD" values, you can follow these steps in Power Query (in Power BI or Excel). Here’s a detailed approach:

 Step-by-Step Guide

1. 1. Create the BNSD Column in Both Tables

Start by creating the concatenated column "BNSD" in both tables:

For Table 1:

1. Open Power Query.
2. Select Table 1.
3. Go to Add Column > Custom Column.
4. Use the following formula to create "BNSD":

   ```m

   [Batch] & "-" & [Nominal] & "-" & [Supplier] & "-" & Text.From([Date])

   ```

For Table 2:

1. Select Table 2.
2. Repeat the same steps to create the "BNSD" column.

1. 2. Count Instances of BNSD in Table 1

Next, you need to count how many times each "BNSD" value appears in Table 1:

1. Select Table 1.
2. Go to Home > Group By.
3. Set it up as follows:

   - Group By: BNSD

   - New Column Name: Count

   - Operation: Count Rows

This will create a new table showing the count of each "BNSD".

1. 3. Create a Unique Reference for Table 1

Now you can create a unique reference by combining the "BNSD" with a counter:

1. Go back to Table 1 (the original table).
2. Go to Merge Queries and merge it with the grouped table (the one with the counts).
3. Use the "BNSD" field to merge.
4. After merging, expand the new column to bring in the count.
5. Now, add a new custom column to create a unique reference:

   ```m

   [BNSD] & "_" & Text.From(List.PositionOf(List.Sort(Table.SelectColumns(Table1, "BNSD"), Order.Ascending), [BNSD]) + 1)

   ```

This formula uses the position of the "BNSD" value in the sorted list to create a unique reference.

1. 4. Create the Unique BNSD in Table 2

Repeat similar steps in Table 2 to add a unique reference based on the merged data from Table 1:

1. Merge Table 2 with the updated Table 1 that has the unique references.
2. Expand to include the "Contract" and the unique identifier.

1. 5. Final Merge

Now you can merge the two tables on the unique reference you've created:

1. Go back to Table 2.
2. Use the Merge Queries feature to merge Table 2 with the updated Table 1 using the unique reference.
3. Expand the merged results to include the "Contract" field.

 Conclusion

By following these steps, you'll create a unique identifier for each "BNSD" value, allowing you to merge the tables without duplicating rows unnecessarily. This method efficiently counts instances and generates unique references for your data merging needs. If you have any questions or need further clarification, feel free to ask!