How to union or append tables with different column order?

[karpushin]

Dear all,

I have 2 excel tables.

Columns in the first table differs from columns in the second table.

I used Query Editor to reorder columns. After application of this step columns order (what I see in Query Editor) in both tables are similar.

However, when I try to use UNION function of APPEND it does not work properly. It just ignore the order of columns.

How to fix it? Thank you in advance for you reply.

Kind regards,

KK

[DavidMoss]

I'd just like to add that you can obviously append tables that don't have all the columns of the master table that you are appending to. As long as the table you are appending for the columns it has that they have the same names and datatypes of its master columns.

ie you could append a table with 2 columns of which are called Column3 & Column 2 to the mater table with the 3 columns.

It would result in a null value for all its rows in Column1.

[Eric_Zhang · Accepted solution]

@karpushin

Here's a sample to append two queries with different columns names.

Code in advanced editor.

Table1

let
 
    Source=Table.FromRecords(
    {
      [columnA= "colA1", columnB = "colB1", ColumnE="ColE1"],
      [columnA= "colA2", columnB = "colB2", ColumnE="ColE2"]
    }
    ),


    AppendColumns=Table.SelectColumns(Source, {"columnA","columnB"}),
    RenameAppendColumns = Table.RenameColumns(AppendColumns,{{"columnA", "columnC"}, {"columnB", "columnD"}}),
    #"Appended Query" = Table.Combine({RenameAppendColumns, Table2})

    
in
    #"Appended Query"

Table2

let
    
    Source=Table.FromRecords(
    {
      [columnC= "colC1", columnD = "colD1"],
      [columnC= "colC2", columnD = "colD2"]
    }
    )
in 
    Source

[SamLester]

Have you tried using the "Append Queries" option in Query Editor? I use this a lot when my spreadsheets have the same format (ex: monthly reports). Here is a quick example where we have two spreadsheets (One ordered Col1, Col2, Col3, the other ordered Col3, Col1, Col2). By using Append Queries, you don't have to do any manual reordering and the appened results will automatically append the correct columns.

Thanks,
Sam Lester (MSFT)

[123117]

Hey,

But what if the number of columns are different and only one columns matches among these tables

[jmdh]

Many thanks for this suggestion. While using the result of  Append vs  Union may differ a little bit, your solution is a very elegant way to solve what seems to be a very bad bug: Move columns in Query is EXPECTED to be refelected later in Data View.

I have used append in the past, but had not realised this value.