Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel. Let us know if you found these tips useful! Hopefully, we helped simplify your workflow and saved you some extra time.Ĭheck out our Data Collection and Transformation page for more information, and feel free to contact us if you require help or support with any of your analytics challenges.You can use Microsoft Query in Excel to retrieve data from an Excel Workbook as well as External Data Sources using SQL SELECT Statements. To update the file as you receive more data, simply:Ī. You now have all the data combined into one file!ġ0. Choose where you want the new table to load (In this example, the data will be loaded to a PivotChart, but you can load yours to wherever you need it) Remove all columns apart from Date and SessionsĨ. You can now perform any extra data transformation steps that you may want to apply to the data before it’s loaded into your new master fileī. You’ll now be taken to the Power Query window, where a number of steps will have been automatically added to combine the files. Check that Power Query has correctly interpreted the sample data file shown, and tweak any settings if necessary, then click OKħ. Enter the file path of the folder where you’re collecting the input data files, or find it via the Browse toolĦ. Data > Get Data > From File > From FolderĤ.

Open up the workbook in which you want to combine the data (we’re using a new blank workbook for this example)ģ. Get your data files in the same folder (it’s easiest if these are the only files in the folder)Ģ. Check that all the files you want to combine are in the same format (column names etc.) If they aren’t in the same format, there are ways to rectify this in Power Query, but for the sake of brevity they won’t be discussed in this article.Ĭ.

It can also be installed as a free add-in if you use Excel 2010 or Excel 2013, but some of the following steps will differ slightly.ī.

Power Query is a built-in feature of Excel 2016 and onwards, which is what we’ll use for this walkthrough.

Get and transform excel 2013