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 .

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:Create a standard list of columns.Apply renaming before combining.Fill missing columns using MissingField.UseNull.

Data Combining Problem | Microsoft Fabric Community

14 Replies

Replies have been turned off for this discussion

Zanqueta

Super User

8 months ago

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

Super User

8 months ago

Ok aTa_Shaikh,

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

Logic for Combining

Import the three tables.
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
Select only the required columns.
Combine all tables using Table.Combine.
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 🌀.

aTa_Shaikh
Frequent Visitor
8 months ago
ronrsnfld

This is result i am expecting .
aTa_Shaikh
Frequent Visitor
8 months ago

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
  Super User
  8 months ago
  Please show what you expect for a result after you have combined those three tables.
aTa_Shaikh
Frequent Visitor
8 months ago
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
  Super User
  8 months ago
  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:
  Create a standard list of columns.
  Apply renaming before combining.
  Fill missing columns using MissingField.UseNull.

ronrsnfld

Super User

8 months ago

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:

Data Combining Problem.pbix25 KB

aTa_Shaikh

Frequent Visitor

8 months ago

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

ronrsnfld

Super User

8 months ago

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
Frequent Visitor
8 months ago
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 !
Rufyda
Super User
8 months ago

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

Regards,
Rufyda Rahma | MIE

Forum Discussion

Data Combining Problem

14 Replies

Logic for Combining

Related content

Combining Column Data

Underlying Error Privacy...combining of data

Tables combine

Problem with transfer data for fabric to mysql

Refresh problem - Unable to combine data