Merge table 1 and table 2 with duplicates

[Hvaldes]

Hello, I'm new to Power Query and need help. I have two tables in Power BI: Table 1 with orders and Table 2 with consolidated information on invoices and dispatch dates. The problem is that in Table 2, order lines repeat due to partial shipments. This makes it difficult to create a unique key column and align it with Table 1, as the Line no column becomes non-sequential due to the partial shipments.

Here is the extract of the data for table 2:

here is the key column from Table 1

ZXCLN-003341-16-90029626

I need to merge table 1 with the information of table 2 but considering the key of table 1 as correct and adjusting the correct line in table 2.

[PwerQueryKees]

What do you mean by "adjusting the correct line in table 2."?

Either way, you want to do a Merge Queries (Table.NestedJoin) which will give you an new column in table1 with all the matching table2 rows.
And then it depends what you are after if that table has multiple rows: The row with the highest line number or highest invoice number? A total? 

Either way:
You add another custom column referencing the column produced by the Table.NestedJoin and select/calculate whatever you need but for each row.

These formulas tend to get a bit cryptic, but it is doable. You could even do it in one step...
If you know what you want, and you want help, please upload some sample data and an example of the expected result.... 

[Hvaldes]

I am sorry if it was not very clear

what I was trying to say was that in table 1, I have a series of columns which form at the end the following key as an example

now if I go to table 2 (Consolidate table), I have equal columns which help me to create the key that allows me to join it with table 1.

Note: Just filter by PURCH column to show an example

In this case the union would be with the row that is marked in green but here I have a problem, because in table 2 there are duplicates (marked in red) which generates me that the index of the line is increasing, which is not correct, then I would like for example that the correct line for in this case the key of table 1 would be 16 in table 2 and not 17.

I hope to be more clear.

[AmiraBedh]

Can you share some textual data ?

[AmiraBedh]

Your data is not clear but here is how you can proceed : after you load both Table 1 and Table 2,  you select Table 2 > go to the "Add Column" tab and select Index Column, so it will will add a sequential index to each row, to maintainin the original order.

Then group by the Order  and Line Number and choose All Rows to keep all data within the group.

Now select Table 1 and Merge Queries and choose Table 2 as the table to merge with where you select the key columns from both tables (the unique key column from Table 1 and the corresponding column from Table 2).

After merging, expand the Table 2 column and select the columns you want to dispkay.

If your share your data, I can provide you the result.