Data Combining Problem

[aTa_Shaikh]

Hi Everyone ! 
I have got a folder which contains many excel files and i have got a task to combine all of them .. 

But The Problem is that the Columns in the Table are not in same order .
Moreover , Many Table have Different Set of Columns Names .. 
So I Really Don't Know How To Deal With It .
I Tried Renaming and Reordering .. However , I didn't got the desired result 
I Hope Anybody Can Help Me .

[Zanqueta · Accepted solution]

Hi @aTa_Shaikh,

Thank you for sharing your code and explaining the outcome. I will be clear and constructive: your approach is valid for a different scenario, but it does not address the original problem you described.

What is different?

• 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.

Why our solution differs:

• 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.

If you want to adapt your logic to this case, you will need to:

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

[Rufyda]

I'm glad you found a solution! We're here in the community to support each other.

Regards,
Rufyda Rahma | MIE

[aTa_Shaikh]

@Zanqueta  , 
Thanks For The Solution 🙂
I was making a silly mistake .. I corrected it and solved the problem . 
Also , is it possible to making it a little more dynamic ? Like can we make it more better mainly the column names ?
If we can't , that is also fine ! 

[ronrsnfld]

Edited to remove extraneous columns

I believe I solved this problem with the following approach, since there does not seem to be any usable algorithm to derive the desired column name from the used name

 - Create a Lookup Table that has the desired column name as column headers, and the various aliases listed in the rows below

 - The Column names in this table will also be in the desired order.

let
    Name={"Name","Sales Person","Sales Guy"},
    Sales={"Sales","Total Money Earned","Total Revenue Generated"},
    Region={"Region","Area","Area Wher"},
    Day={"Day","Type Of Day","Day Of Sale"},
    Profit={"Profit","Total Earning", "Profit Earning"},
    Supplier={"Supplier","Supplier Person Name","Supplier Gr"},
    Table=Table.FromColumns({Name,Sales,Region,Day,Profit,Supplier},
        type table[Name=text,Sales=text,Region=text,Day=text,Profit=text,Supplier=text])
in
    Table

Lookups:

Write a function to "Normalize" the names (and name it: fnNormalizeNames). 

The input is a list of the column names for the current table, and the output is list of lists of renaming options.

(ColNames as list)=>

let 
    LookupTable=Table.Buffer(Lookups),
    Desired=List.Buffer(Table.ColumnNames(LookupTable)),
    Matches = List.Accumulate(
                ColNames,
                {},
                (s,c)=> s & {[a=Table.AddColumn(LookupTable,"Match", 
                                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)

 - With the above, a script to combine as many tables as you have becomes easier, but you will need to input a list of tables (as in the Source step):

let
    Source = {TableA, TableB, TableC},
    Renames = List.Accumulate(
        Source,
        {#table({},{})},
        (s,c) => s & {
            Table.ReorderColumns(
                Table.RenameColumns(c,fnNormalizeNames(Table.ColumnNames(c))),
                Table.ColumnNames(Lookups), MissingField.UseNull)}),
    Combine = Table.Combine(Renames),

//Remove Extraneous columns    
    Remove = Table.SelectColumns(Combine, List.Intersect({Table.ColumnNames(Combine), Table.ColumnNames(Lookups)}))
in
    Remove

 Results:

[aTa_Shaikh]

Hi @ronrsnfld  , 
Can you Please explain me how does this custom function works ? 

[ronrsnfld · Accepted solution]

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.

[aTa_Shaikh]

Thanks For The Solution .. 
However , its too high level for me .. 
Currently , I am Using Mapping Table Technique to do the combining that is also working fine . 
but , i want to ask .. is there any way of making it automated? 
I have to do a lot of manual work and rename all of the columns again and again .. 
is there any way of making it easier ? 
Especially with the method that zanqueta shared ?
If you can help me with that .. it would make things a lot more easier for me . 
Since i am using that method to combining the data . 
Thanks Once again . 



 

[ronrsnfld]

I don't know what you mean by "automated" in this context.

For my solution, you merely need to enter the alias column names in the appropriate column of the lookup table. If there were some commonality between the names, then it might be possible to do this programmatically, but you have not shown that there is.

And using my complete solution, once you create your lookup table, you would not have to do any manual renaming; that would be done by the procedure itself along with combining the tables.

So far as modifying the solution of @Zanqueta , you should discuss that with him.

[aTa_Shaikh]

@Zanqueta 
Thanks For The Reply .. I Used Your Code and Implemented it my case .. 
However , Didn't got the expected result .
Here , is my m code .

let
Source = Folder.Contents("Source"),
BinToTables = Table.TransformColumns( Source , {"Content" , (x) => Excel.Workbook(x , true) , type table} ),
ExpandTbl = Table.ExpandTableColumn(BinToTables, "Content", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
Columns = Table.ColumnNames( ExpandTbl[Data]{1} ),
Renames = Table.TransformColumns( ExpandTbl , 
{"Data" , (x) =>  
Table.RenameColumns( x , List.Zip( {Table.ColumnNames(x) , Columns}) , MissingField.Ignore) , type table} ),
Combine = Table.Combine( Renames[Data] )
in
Combine

 Here's What I Ended Up As an Output : 
 I believe Code Takes Care of renaming part .. However , Since the columns are unorder it causes a huge problem. 
 Usually , When Columns are unorder we use Table.ColumnNames(Table) and make them in perfect order 
 But , In this case we have different headers for all the columns .. 
 We Can't Reorder them First and Rename them later .. 
 

[Zanqueta · Accepted solution]

Hi @aTa_Shaikh,

Thank you for sharing your code and explaining the outcome. I will be clear and constructive: your approach is valid for a different scenario, but it does not address the original problem you described.

What is different?

• 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.

Why our solution differs:

• 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.

If you want to adapt your logic to this case, you will need to:

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

[aTa_Shaikh]

@ronrsnfld 
 
 This is result i am expecting . 

[aTa_Shaikh]

Hello @Zanqueta ,   
Here , I have Shared 3 Different Tables .. All of Tables Have Different Column Names and Different Orders .. 
 Yes , The Other Sets of column names can be disregarded . 

[ronrsnfld]

Please show what you expect for a result after you have combined those three tables.

[Zanqueta]

Hello @aTa_Shaikh,

I’m not concerned about the column order, as long as the standard names remain consistent. Do you have a predefined set of column names you expect to combine? Could you share a sample of the headers?
Additionally, should the other sets of column names be disregarded?

[Zanqueta]

Ok @aTa_Shaikh, 

Your files can be combined, but let me know if theses sugestion over renames are possibles:

Logic for Combining

1. Import the three tables.
2. Rename columns to a common standard:
   • Supplier Person Name → Supplier G
   • Sales Person or Name → Sales Guy
   • Profit → Profit Earning
   • Total Money Earned or Total Earning → Total Revenue Generated
   • Area or Sales Region → Area Where
   • Type Of Day or Day → Day Of Sale
3. Select only the required columns.
4. Combine all tables using Table.Combine.
5. Handle missing columns with MissingField.UseNull.

So, if these could be an approach, adapt and test the script bellow:

let
    // 1. Import tables (replace with actual file paths)
    SourceA = Excel.Workbook(File.Contents("C:\Data\a.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data],
    SourceB = Excel.Workbook(File.Contents("C:\Data\b.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data],
    SourceC = Excel.Workbook(File.Contents("C:\Data\c.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data],

    // 2. Rename columns to standard names
    TableA = Table.RenameColumns(SourceA, {
        {"Supplier Person Name", "Supplier G"},
        {"Sales Person", "Sales Guy"},
        {"Total Earning", "Profit Earning"},
        {"Total Money Earned", "Total Revenue Generated"},
        {"Area", "Area Where"},
        {"Type Of Day", "Day Of Sale"}
    }, MissingField.Ignore),

    TableB = Table.RenameColumns(SourceB, {
        {"Supplier", "Supplier G"},
        {"Name", "Sales Guy"},
        {"Profit", "Profit Earning"},
        {"Sales Region", "Area Where"},
        {"Day", "Day Of Sale"}
    }, MissingField.Ignore),

    TableC = Table.RenameColumns(SourceC, {
        {"Supplier", "Supplier G"},
        {"Name", "Sales Guy"},
        {"Profit", "Profit Earning"},
        {"Sales Region", "Area Where"},
        {"Day", "Day Of Sale"}
    }, MissingField.Ignore),

    // 3. Select final columns
    FinalColumns = {"Supplier G", "Sales Guy", "Profit Earning", "Total Revenue Generated", "Area Where", "Day Of Sale"},
    TableA2 = Table.SelectColumns(TableA, FinalColumns, MissingField.UseNull),
    TableB2 = Table.SelectColumns(TableB, FinalColumns, MissingField.UseNull),
    TableC2 = Table.SelectColumns(TableC, FinalColumns, MissingField.UseNull),

    // 4. Combine all tables
    Result = Table.Combine({TableA2, TableB2, TableC2})
in
    Result

Please, let me know if it worked

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.