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.
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
TableLookups:
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
RemoveResults:
Hi ronrsnfld ,
Can you Please explain me how does this custom function works ?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.
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 .
