Results 1 to 3 of 3

Thread: Append Queries with different Colulmn Names

  1. #1
    Neophyte asjones987's Avatar
    Join Date
    Feb 2020
    Posts
    1
    Articles
    0
    Excel Version
    2019

    Append Queries with different Colulmn Names



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have 2 queries with very different headers and many columns each. However I have rearranged things so the data is the same.

    Is there a simple way to append the two queries? When i tried with the current data set it would put the tables "next" to each other with NULL in the extra spots.

    I wanted to "demote" the headers in each table and force the generic COLUMN1, COLUMN2,..... COLUMN36, then try the merge but can't find a good way to demote headers and let the auto headers get generated.

    Any thoughts?

    Thanks

    Alan

  2. #2
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    101
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Use the List.Zip trick to rename all columns in Table2 from Table1, then append

    Code:
    let
    
        FirstTable = Table1,
        OtherTable = Table.RenameColumns(Table2,List.Zip({Table.ColumnNames(Table2),Table.ColumnNames(Table1)})),
        Combined= Table.Combine({FirstTable, OtherTable})
    in
    Combined

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,355
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Or, if you want to avoid custom code...

    Providing the columns are all in the same order, the "Use First Row as Headers" button is a split button and the other option is "Use Headers as First Row", which does demote the headers into the data. If you were to do that on both tables, you could then append them. (You may want to remove the top 1 row from one of the queries after doing it so that you don't end up with the second table's header in the middle of your data.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •