Compare Column Names from Multiple Sources

[gadao]

Hi there,

I was wondering if someone could give me some ideas in creating a matching col names table.

The ideia is simple, but i can't figure out.

This table would have n columns, each column representing a table in your PQ.

The rows should show the column names for each 'column table', but it should bring side by side when the column name is the same across tables.

Table 1	Table 2	Table 3	Table 4
A	A	A	A
B		B
C			C
	D	D
E
F
		G	G
H	H	H

So far I tested some ideas over these functions:

Table.Pivot

Table.UnpivotOtherColumns

Table.FromList

Table.ColumnNames

Thanks for helping

[dufoq3]

Hi @gadao, another solution:

You have to define your tables here:

Output:

You can delete steps Table1 - Table4 (they are just as sample tables).

let
    Table1 = #table(Text.ToList("ABCEFH"), {}),
    Table2 = #table(Text.ToList("AHD"), {}),
    Table3 = #table(Text.ToList("HGABD"), {}),
    Table4 = #table(Text.ToList("GAC"), {}),
    DefineTablesHere = [ //I do not know your table names, but you have to specify all of them here.
    Table1 = Table1,
    Table2 = Table2,
    Table3 = Table3,
    Table4 = Table4
  ],
    TablesColNames = List.Transform(Record.ToList(DefineTablesHere), Table.ColumnNames),
    AllColNames = List.Buffer(List.Sort(List.Distinct(List.Combine(TablesColNames)))),
    TableMap = Table.FromColumns(List.Transform(TablesColNames, each List.Transform(AllColNames, (x)=> if List.Contains(_, x) then x else null)), Record.FieldNames(DefineTablesHere))
in
    TableMap

[AlexisOlson]

You 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:

[RandomAxiom]

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

[RandomAxiom]

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. 

[Anonymous]

You could make a Table.ColumnNames(TableName) for each table, and then use List.Zip to so that each column name is in the same position in the list, like:

= List.Zip({ColumnList1, ColumnList2, ColumnName3})

Then make it into a table using Table.FromRows(TheListZipQuery)

--Nate