locked
Can't get my head around Power Query Append RRS feed

  • Question

  • I have 1 spreadsheet with 6 almost identical tabs. I have added each one to PowerQuery and pre-processed it.  The result of these 6 queries are 6 identical tables.  Now I want to combine them into a single table an import into my data model.

    I see there are 2 approaches but neither are intuitive to me. I just want a single query that I select "import to data model" that will append all the data from the other 6 queries.

    Can someone give me a step by step guide?  All the sites I have looked at tell me how to combine 2 queries but I can't seem to scale the concept.

    I realise this seems like a really dumb question - sorry in advance.

    With hind site, I think I could use this approach 

    datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/

       I will probably do that anyway.

    But I would still appreciate some guidance on the best way to append my 6 tables into 1.

    Wednesday, February 26, 2014 9:29 AM

Answers

All replies

  • If your tables are called Table1, Table2, Table3, Table4, Table5 and Table6, the easiest thing to do is to create a new blank query (by clicking From Other Sources/ Blank Query button in the toolbar) and entering the following into the formula bar in the Query Editor:

    = Table.Combine({Table1, Table2, Table3, Table4, Table5, Table6})

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, February 26, 2014 10:08 AM
  • yes, that is exactly what I need - thanks.  I am just surprised that the UI assisted tools do execute this step are not intuitive.    

    I have downloaded the M reference guide and had a cursory review last night.I didn't see a lot of reference to functions (or what ever you call Table.Combine)  Is there a reference for such functions anywhere that you could direct me to?

    Appreciate your help.

    Thursday, February 27, 2014 12:30 AM
  • There should be a link to a library reference in the same place where you found the language reference. If not, a search engine should take you there with "Power Query Library Reference".
    Thursday, February 27, 2014 4:58 AM
  • Here's a cheat sheet of all the functions that you might find convenient.

    http://office.microsoft.com/en-us/excel-help/power-query-formula-categories-HA104122363.aspx?CTT=5&origin=HA104003813

    HTH

    Faisal Mohamood | Program Manager | Data Platform Group - Microsoft

    Thursday, February 27, 2014 5:20 AM
  • Thanks to you both. I was searching for power query reference guide, and came up blank each time. Funny thing is I just ran that search and found this post :-)
    Thursday, February 27, 2014 3:07 PM
  • Hi Mally,

    Thanks for the feedback. Besides typing in the formula as Chris, Curt and Faisal mentioned, you could also apply Append Queries operations to append tables one by one. Note that if you launch "Append Queries" from the Power Query ribbon tab you will get a new query every time, but if you do it from the Query Editor dialog ribbon, Power Query adds every Append operation as a new step within the current query.

    We are well aware that this 1-by-1 append operation is a shortcoming in the Power Query UX and have plans to address it by letting users add more than one query (i.e. more than one dropdown) in the Append Queries dialog, which will effectively generate a similar formula to what others on this thread have proposed.

    We still don't have details on the timeline for the availability of this improvement to Append Queries, but I wanted to let you know that we are planning to address it.

    Thanks for your feedback and for using Power Query.

    Regards,
    M.

    Thursday, February 27, 2014 3:49 PM
  • How far is the rectification of the very cumbersome Append interface?

    Sign here

    Monday, September 14, 2015 3:36 PM