Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to combine multiple text file Data using Power Query in Excel. Let’s get started
SAMPLE DATASET
The screenshot below shows all the 4 text files that we want to combine. They are located in the Sales Data Folder
COMBINE THE TEXT FILES
To combine the text files:
- In the Get & Transform Data group of the Data tab, click on Get Data and Select From File
- Next, Select From Folder and browse through the text files location
- Select all the text files using CTRL + A and click Open
- In the Navigator dialogue box, we can see all the 4 text files and their properties as seen below
- Select Combine & Transform Data at the bottom of the navigator dialogue box
In the Combine Files dialogue box, we can also see the data preview
- Click OK.
From the screenshot below, the data are successfully combined in the Power Query editor and all the APPLIED STEPS are generated automatically.
Finally, we can load the data into the Excel worksheet for further analysis or consumption as the case may be. To load to the worksheet:
- In the Home tab of the Power Query editor, select Close & Load To…
- In the Import Data dialogue box, the default table is fine. Select Existing worksheet
- Click OK
From the screenshot below, we have successfully combined the data