Tables combine

Hi Mederic 

I would use below strategies if in your situation. 

1. Create one reusable Function for data cleaning as below and apply this function on each table

(table) =>
let
    Source = table,
    HeaderRow = List.First(List.Select(Table.ToRows(Source), each List.Contains(_, "Document No"))),
    HeaderRowIndex = List.PositionOf(Table.ToRows(Source), HeaderRow),
    PromoteHeaders = Table.PromoteHeaders(Table.Skip(Source, HeaderRowIndex)),
    RemoveNulls = Table.SelectRows(PromoteHeaders, each [Document No] <> null and [Document No] <> ""),
    SelectColumns = Table.SelectColumns(RemoveNulls, {"Document No", "Order", "Amount"}, MissingField.UseNull)
in
    SelectColumns

2. Use Table.Combine or 'Append Queries as New' from UI to combine all your tables.

3. Add a custom column that returns the Document No only for rows where it's a number, and blanks for name rows

4. Fill down the Document No so each order row gets matched with the correct document.

5. Remove rows where both Order and Amount are blank. 

6. Move the No column to the left as your new Document No column. Optionally, you can also remove the original Document No column if it’s no longer needed.

You can also wrap Steps 2–5 into another function if you're repeating this logic across multiple datasets.

In case you would like to refer to this file, please use this link

Hope this helps:)