Power Query: Abridged Appending
24 January 2018
Welcome to our Power Query blog. This week, I take a look at a way to create and append new queries by starting with the Query Editor.
Way back in December 2016, one of my introductory blogs looked as how to combine CSV files (Power Query – Appending Files). Whilst this method is fine for files that appear at different times, sometimes I have a group of files that I need to append quickly. For this example, I am assuming that the files are not all in the same folder (as that is covered in Power Query – One Folder, One Query). I start with a new empty workbook, and go to the ‘Data’ tab.
I have an option to call the Query Editor which I can access from the ‘New Query’ dropdown in the ‘Get and Transform’ section. The ‘Launch Query Editor’ option is in the ‘Combine’ Queries’ section (the ‘Launch Query Editor’ option is also available in Excel 2013 as an icon on the ‘POWERQUERY’ tab).
This view is clearly different from the ‘Blank Query’ option. There is no formula bar or ‘Query Settings’ pane. The purpose of this format of the editor is to allow me to create and combine queries, so I begin by using ‘New Source’ to locate my file:
The options are similar to the query creation from the ‘Data’ tab in my Excel workbook. I choose ‘Text/CSV’ and locate the file I will begin with.
A preview of my file appears and I can click ‘OK’ to create a query.
My file is extracted. The formula bar and ‘Query Settings’ pane now appear (if these do not appear then on the ‘File’ tab there is a ‘Query Options’ screen where I can choose my view – more on query options in Power Query: Querying Query Options). I repeat this process to get queries of five CSV files that I want to combine. I choose the ‘Append Queries’ option.
I am happy to add the other data to this query, so I choose the ‘Append Queries’ option:
I opt to append three or more tables, and I can choose from any of my queries – my current query is already included, so I choose the other four.
I select ‘OK’ to continue.
All my queries are appended in one step and are ready for me to transform. This is a useful way of quickly appending new data that is extracted from different folders.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.