Forum Discussion

karpushin's avatar
karpushin
New Member
10 years ago
Solved

How to union or append tables with different column order?

 

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

  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    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

     

5 Replies

  • SamLester's avatar
    SamLester
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    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)

    • jmdh's avatar
      jmdh
      Icon for Advocate IV rankAdvocate IV
      9 years ago

      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.

    • 123117's avatar
      123117
      Frequent Visitor
      4 years ago

      Hey,

       

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

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    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

     

  • DavidMoss's avatar
    DavidMoss
    Icon for Advocate V rankAdvocate V
    10 years ago

    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.