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.
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
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.
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.
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.
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.
Find one of the files in your SharePoint Folder. Click on the word “binary” for that file.
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
Save the Power Query
Create a new query to import all the files located in your SharePoint folder
Once you have the files you need, create a custom column that uses your function produced in the first step above. Click OK
Expand the new custom column. Click as indicated. Make sure “Expand Column” is selected and then click OK.
You should end up with your consolidated table of data something like this.
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/