Forum Discussion
Data Combining Problem
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.
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.
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?
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 🌀.