Consolidate Multiple Excel Files in SharePoint using PowerQuery

I had read an interesting request on the Whirlpool forum over the weekend. Whirlpool user Alicia2 wanted to extract Word document metadata into a Spreadsheet. That seemed like an interesting problem and something I could use some time. So I decided to help her out.

I built this Spreadsheet that you put into a folder along with the Word documents (DOCX format). The Spreadsheet then uses Power Query to suck in the file names in the folder (manual refresh required). Finally you click the Extract Metadata button, and the Spreadsheet does the rest.

Word Metadata Extractor

Here is the extract word metadata Spreadsheet.

And here is a video that shows you how it works.

I found this Post that explains how to consolidate files on your computer with Power Query (kudos to Bacon Bits). That is a great tip, however I wanted to consolidate multiple Excel files in SharePoint using PowerQuery – not files on my computer. I used the principles in the link above to come up with the following procedure.

EDIT 21/11/14: Today Chris Webb posted this blog which covers an easier way to combine files on your PC.

Create a new folder in your sharepoint site. Add all the files you want to consolidate into this folder. Make sure they are all identical in file format, because we are going to consolidate them into a single file using a repeatable process – they must be identical in layout. Note: do not add any other files to this folder.

Create a SharePoint Consolidation File

Sample Excel File to Store on Sharepoint

Note that it is laid out in a way that is easy for the user to edit the numbers (ie Crosstab format). This is good for data entry, but it is not good for PivotTable reports. The next tip will help you fix that. You will want to create multiple copies of this data input file – one for each data source (in my case it is each person that manages a budget).

Import the Data from SharePoint into Excel Using Power Query

Open a new Excel Workbook
Go to Power Query
Select From Other SourcesFrom SharePoint List.

Connect to the SharePoint files using PowerQuery

Paste the URL of your main SharePoint Site. Note well: You must use the main URL, not the URL of your Shared Documents or any other sub page.
Click OK
Go to the Workbook on the right hand side and right click on the SharePoint URL. You don’t have to wait for it to refresh. Righ click/edit.

edit link

Make sure you can see the formula bar at the top. If you can’t, click View and then select the Formula Bar Checkbox.
In the formula bar, change Sharepoint.Tables to read Sharepoint.Files . Note this is case sensitive.

Change Code from SharePoint.Tables to SharePoint.Files

Find one of the files in your SharePoint Folder. Click on the word “binary” for that file.

click binary
It should return something like Name = Sheet1, Data = Table. Click on Table

click table
Edit the data in the table as you normally would with Power Query.

Once you have the data the way you want it in Power Query, turn the query into a function. First rename the Query to something like fGetMyFilesSharePoint. Then go to the Advanced Editor. In the Ribbon, click ViewAdvanced Editor. Add the code as shown below

create-function-2

create-function-1

Save the Power Query

Create a new query to import all the files located in your SharePoint folder

create-new-query

Once you have the files you need, create a custom column that uses your function produced in the first step above. Click OK

custom column

 

Expand the new custom column. Click as indicated. Make sure “Expand Column” is selected and then click OK.

expand column

You should end up with your consolidated table of data something like this.

final table

If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-excel-power-bi-online-training/

Share?

Comments

  1. PowerQuery is very interesting BUT it’s major problem is total lack of refresh if you want to publish it on sharepoint

  2. I had heard this Patrick, but never tried it myself. In my use case here, I am using Power Query on the desktop client and not on SharePoint, so it is not really an issue. I agree with that this is a big limitation. Have you given feedback? There is an option in the Ribbon under “Power QuerySend Feedback”.

  3. Matt,

    I’m trying to “Find one of the files in your SharePoint Folder”. I have many files on the SharePoint site, and they are buried many folders deep. Any idea why my folder isn’t showing up? Thanks!

    Lisa

    • And to add to that…I know they’re not showing up because the preview window can’t show every one of my many SharePoint files in the preview. How do I get around this? Thanks!

      Lisa

      • Yes, I only have one Excel file in my PowerQuery preview, so I’m only getting one filter result even though I have hundreds (if not more) of Excel files on my SharePoint site. Any other ideas?

        Thanks!
        Lisa

        • Sorry, hard to say without looking at it. Are you saying there are hundreds of files on your SharePoint site and only 1 shows up, or are you saying there are hundreds of files and only 1 XLSX shows up? If the latter, try clicking on the Extension filter after changing the M code to SharePoint.Files(…)

          Power Query only brings in a sub set of records, so if you have LOTs of files, that could be the issue

        • Lisa,

          Try using “Sharepoint.Contents” instead of “Sharepoint.Files”. This should give you a list of all of your “folders in SP, rather than all of your files, and you can drill in from there. It may help you narrow down the query enough so that you can see the files you are looking for.

  4. Hi,

    the solution above would save me many hours manual work right, but I don’t get it to work 🙁 The problem is, as I see it that the formula I created is not loaded into the sheet. in the workbook queries tab stands “Load is disabled” below the formula query. Do you have any idea how I can enable the load?

    Thanks in advance

    Frank

  5. I am getting lost between createfunction2.png and createfunction1.png. Are you creating 2 separate queries?

    After following createfunction2.png ; it asks me to invoke function? when I close and load without invoking, I get “Connection Only” under the function in the workbook query. If I invoke it asks me to enter parameter “FileName”, leaving blank and clicking okay gives me an error “Expression.Error: The Key did not match any rows in the table.”

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x