cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karpushin
New Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: How to union or append tables with different column order?

@karpushin

 

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

 

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

 

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

Re: How to union or append tables with different column order?

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.

 

 

AppendedQueries.PNG

 

Thanks,
Sam Lester (MSFT)

Microsoft
Microsoft

Re: How to union or append tables with different column order?

@karpushin

 

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

 

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

 

View solution in original post

DavidMoss Advocate V
Advocate V

Re: How to union or append tables with different column order?

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.

jmdh Advocate III
Advocate III

Re: How to union or append tables with different column order?

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors