Data Combining Problem

Hi aTa_Shaikh,

• Your code assumes that all tables have the same column names, only in a different order. For that reason, it uses Table.ColumnNames from the first table to apply to the others.
• In the original scenario, the tables have different column names and different sets of columns. When this happens, simply reordering is not enough — you need to normalise the names and ensure that all expected columns exist, even if some are filled with null.

• It defines a renaming map to harmonise column names.
• It uses Table.SelectColumns(..., MissingField.UseNull) to align all tables with a standard list of columns.
• It then combines everything with Table.Combine, ensuring consistency.

1. Create a standard list of columns.
2. Apply renaming before combining.
3. Fill missing columns using MissingField.UseNull.

Below is an annotated version of the custom function. Please ask if you have a specific question

//Input the list of Column Names for the Table that is being processed
(ColNames as list)=>

let 

//Read in and Buffer the Lookup Table you created
    LookupTable=Table.Buffer(Lookups),

//Create a List of the Column Names of the Lookup Table which will be the Desired names
//  for each column
    Desired=List.Buffer(Table.ColumnNames(LookupTable)),

//By adding a custom column to the Lookup table, we can search each row for 
// each of the Column Names. 
// When we determine in which column a name is located, we return the column header of that
//   column which must be the desired name
    Matches = List.Accumulate(
                ColNames,
                {},
                (s,c)=> s & {[a=Table.AddColumn(LookupTable,"Match", 

                //Note the Record.FieldValues(_) returns the entries on each row being
                // processed by the AddColumn method
                                each List.PositionOf(Record.FieldValues(_),c))[Match],
                              b=List.Select(a,each _ >=0),
                              x= if List.IsEmpty(b) then null else {c,Desired{b{0}}}][x]})
in 
  List.RemoveNulls(Matches)

Also note that the entire algorithm will handle extraneous columns and missing columns. You will find a missing column by the fact that that merged table will show null instead of a value.

That might mean either that the data is not present at all in the table, OR that you need to add an alias to the relevant column in the Lookup Table.