Forum Discussion
Compare Column Names from Multiple Sources
Super UserYou can write a custom function that takes a list of table names and a column to compare and outputs this result.
Custom function CompareTableColumns:
(table_names as list, column as text) as table =>
let
Tables = Record.FieldValues(Record.SelectFields(#shared, table_names)),
TableCols = List.Transform(Tables, each Table.Column(_, column)),
Union = List.Sort(List.Distinct(List.Combine(TableCols))),
MatchItems = List.Transform(Union, (u) => List.Zip(List.Transform(TableCols, each List.Select(_, each _ = u))){0}),
ToTable = Table.FromRows(MatchItems, table_names)
in
ToTable
This creates a sorted, distinct union of the columns and then stacks the table columns side-by-side based on this union using a method similar to what Anonymous proposed.
You can invoke the function like this:
CompareTableColumns({"T1", "T2", "T3", "T4"}, "Col1")
where each of the tables looks similar to the T4 example:
AlexisOlson I'm trying to do precisely this, but I'm getting an error relating to the column field ("Col1"). Can you explain the role of the column and "Col1" fields in your sample code?
I'm attaching screenshots of my queries in case they shed light on the error I'm receiving. Thanks in advance for any insight.
- AlexisOlson
Do all of your test tables have a column named "Col1"? If not, that's your problem.
Thanks AlexisOlson. I figured out the problem.
Unlike the OP's tables (which are a single column containing heading names), mine were pivoted. For each of my tables, I had to use Table.ColumnNames to generate a list of columns, and then delete the other columns before running the custom function.