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:)

The problem is that the column headings are not always in the same place.

That is not a problem.  Use Table.Combine or the & symbol, it will match the columns from all tables no matter the column order.

Here is a little different approach.

First, create a custom function to extract the relevant data from your table:

This extracts the relevant column, starting at the location of the header

We will apply the special handling for the "Document" column in the main query.

//rename "fnExtractCol"

(rawTable as table, colHeader as text)=>                
    
    [a=Table.ToColumns(rawTable),
     b=List.FindText(a,colHeader){0},
     col=List.Skip(b,List.PositionOf(b,colHeader)+1)][col]

And the Main Query

  Use List.Accumulate and the custom function to process each table and combine them

let

//Changew next lines to create a list of all your tables
//to be used in the List.Accumulate function

    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    
    Tbls = List.Accumulate(
                {Source1,Source2,Source3},
                #table({},{}),
                (s,cur)=> Table.Combine({s,
                    [a=fnExtractCol(cur,"Document No"),
                     b={List.First(a,2)},
                     c=fnExtractCol(cur,"Order"),
                     d=fnExtractCol(cur,"Amount"),
                     e=Table.FromColumns({b,c,d},
                            type table[Document No=Int64.Type, Order=text,Amount=Currency.Type]),
                     f=Table.FillDown(e,{"Document No"})][f]}))
                    

in
    Tbls

From your data in your most recent sample file:

Hello MasonMA ,

Thank you again for your help.
I'm not sure I can apply some of your steps to my object of Tables without expanding them.

I'will using a Text parameter and adding it directly to my Source.
I've tested it on several invoices and "Document No" always appears in column 2 and on the 3rd line.
I'll use this method even if it's not ideal.

Have a good day.
Best regards,

Hi Mederic 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Hi Mederic 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

Hi Mederic 

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards