How to combine multiple files using Power Query / M within Power BI

In this example, I'm going to combine the files contained within a folder using PowerQuery/M in PowerBI.

Below is the folder and the 4 files contained within the folder.

Each of these files has an identical structure of 2 columns, Column A contains the 'type' (A, B, C or D) and column B contains a 'Value' (between 1 and 100).

In order to combine this data we need to do the following:

Open PowerBI, click on 'get data' then click on 'More'

This will open the 'Get Data' window which contains a list of all the connectors available in PowerBI

Select 'Folder' and then click 'Connect

This will then present the 'Folder' window, where the folder reference needs to be added. In this instance, the folder name is 'C:\Example\FilesToCombine'

Click OK, this will then display a window listing the files in the folder and provide some further options

The options at the bottom of the window provide a few different methods to proceed, in this example we're going to click 'Combine' and then 'Combine & Transform'

Next you'll see the following window, click on 'Sheet1' and then click 'ok'

After a couple of seconds, the PowerQuery Editor window will be displayed and a table containing 3 columns will be visible, as shown here

If there are additional transformations required, then they can be carried out in this window
To conclude the process, click on the button labelled 'Close & Apply' in the top left part of the ribbon to load the data to the data model

PowerBI will then provide the progress of the query loading and after a couple of seconds the data will be loaded to the data model.
To access the data, click on the 'Data' icon on the left side of the screen (highlighted below)

Then click on one of the columns in the Fields section on the right side of the window (to display the data
The data will now be displayed in the PowerBI Data view

If you would prefer to export this data to a new table, one option is to right-click on the table and select 'Copy Table' to copy the table to the clipboard. This can then be pasted into an excel sheet or any other place you wish to use the data

Note - this method can be refreshed within PowerBI and it will import all the files within the folder and combine them into a single table. As long as all of the files in the folder have the same format and structure this query will work fine.

Comments

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work. The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article) Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...

Red Gig

Search This Blog