I got a comment on my blog the other day from David, asking if we could make Power Query prompt for a folder at refresh, allowing us to choose which folder to should be used to consolidate files. That sounds like a pretty cool idea, and my first thought was “I think we can totally do that!”
I headed over to Chris Webb’s blog, as he’s done some cool things with Power Query prompts. Ultimately though, I got stuck on one thing… we have to go into the Query to invoke it. My (albeit limited) tests left me unable to have that prompt when we refresh the query.
Not to be outdone by a lacking feature though, I cooked up a solution using some Power Query and VBA techniques that I’ve shared before. This post wraps them up into a neat little package.
Background
The idea I’m going for here is to be able to click a button which:
- Prompts the user to select a folder,
- Feeds that folder into Power Query, and
- Refreshes the Power Query output table.
To set it up, I used 3 techniques that I’ve shared before:
- Building a Parameter Table for Power Query
- Implementing a Browse For Folder solution in VBA
- The concept from Refresh Power Query with VBA but this time for a single table only
Initial Setup
To begin with, I created a blank workbook, and immediately added a Power Query parameter table as outline in Building a Parameter Table for Power Query. I followed the steps there exactly, so I’m not going to detail that here. The only things of note were:
- I named the worksheet “Parameters”, and
- I used the following for my “File Path” value: D:\Consolidate\Begin
Next, I created a query to consolidate all the files in a folder. Basically I ran through the following steps:
- Power Query –> From File –> From Folder
- Chose my folder: D:\Consolidate\Begin
- Did a bit of cleanup on the file
- Implemented the “fnGetParameter” function call in place of the folder (as described in the aforementioned blog post)
The end result was some M code that looked like this:
let
Source = Folder.Files(fnGetParameter("File Path")),
#"Combined Binaries" = Binary.Combine(Source[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",null,",",null,1252),
#"First Row as Header" = Table.PromoteHeaders(#"Imported CSV"),
#"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"TranDate", type date}, {"Account", Int64.Type}, {"Dept", Int64.Type}, {"Sum of Amount", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Sum of Amount", "Amount"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Amount", type number}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"TranDate"})
in
#"Removed Errors"
And returned results like this:
I then loaded the Power Query to the worksheet, and called the worksheet “Data”.
So far so good? Nothing really unusual yet.
Laying the Folder Change groundwork
With the basic functionality in place, I wanted to now give the user the ability to change the folder.
Rather than write the Browse For Folder routine from scratch, I headed over the VBAExpress.com and grabbed the one I submitted to the KB… um… a long time ago. 🙂
Once I had that code copied I:
- Opened the VBE
- Browsed into my workbook
- Right clicked the project and added a new Module
- Pasted in all the code (shown below):
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
'Destroy the Shell Application
Set ShellApp = Nothing
'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function
With that in place, I just needed to link that function into a routine that can use it…
Rolling the Final Routine
I write a lot of VBA, so this was pretty quick to knock up for me. The code itself looks like this:
Sub UpdatePQ()
Dim vFolder As Variant
Dim wsParameters As Worksheet
Dim wsData As Worksheet
'Set the worksheets
Set wsParameters = Worksheets("Parameters")
Set wsData = Worksheets("Data")
'Update the folder to import
vFolder = BrowseForFolder
If CStr(vFolder) = "False" Then
'No folder chosen, exit the routine
Exit Sub
End If
'Update parameter table with folder
wsParameters.Range("B2").Value = vFolder
wsData.Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub
In short, this routine basically:
- Sets references to the two worksheets
- Prompts the user for the folder. The output here could be a folder path or “False” which means the user cancelled. If it’s “False” we bail out
- If the path was provided, it gets placed in B2 of the Parameter worksheet (which is the “Value” field for the “File Path” variable. (So if you didn’t create your table starting in A1, then you’d need to update this.)
- Finally, we refresh the Power Query output table which I landed in cell A1 of the Data worksheet.
Final Setup Step
The very last step is to link the UpdatePQ macro to a button. This is fairly straight forward as well, and the steps are illustrated well in the Refresh Power Query with VBA post.
Make Power Query Prompt for a Folder at Refresh
And now, the only thing we need to do is click a button…
Choose a folder…
And the query will refresh the data in the landing page.
Try it out
You can download the completed workbook, as well as two folders with the source data, from my Skydrive. Click here to do so.
Caveats
I hope it goes without saying that the data structure of the files in the selected folders must be the same. If they’re not, the query load will fail, of course. But so long as you’re just trying to deal with different months/years, this should work nicely.
17 thoughts on “Prompt for a Folder at Refresh”
Ken
Thanks for sharing this - this is really great stuff and is inspiring me to do all sorts of things with PQ that I hadn't even considered before - very dangerous territory! 🙂
Thanks again
David
LOL! Use your powers for good! 😉
I've just attended a course on Power BI and am inspired to dig into these powerful tools and really start using them more effectively and accurately now; esp. since these are being integrated into Office (Excel) 2016 along the way. These tools are here to stay and its now or never to jump in (in the deep end :-))
TX for this solution Ken; adding variables to repetitive processes are always handy and this is some good learning and exposure.
To all you BI gurus, Ken, Chris, Marco and Alberto...just a word of TX for your blogs, your time and the unreserved way to continue to share your knowledge and tips with us mere mortals. Keep up the great work as we all appreciate its value.
Cheers
Rudi
Sorry for the extra post, but I had to credit Rob and Kasper too as I have referenced their work in the past too...sorry for that oversight! Cheers guys!
No worries, Rudi! Glad the material is helping you out, and I'm always happy to see my colleagues called out in thanks. 🙂
Ken
I have been playing with this and have come up with a problem that I don't think Power Query can solve - but I thought I would ask anyway.
The Data I have is from a SAP database, and the output is onto worksheets with 20 rows of extraneous data before the column headings. The Data is not in a table, although could easily be... but I am trying to keep this simple for my collogues who know even less about Excel than I do (and that is not saying much).
Is there a way when creating the query to remove the first 20 rows when creating the query from a folder? I can do it easily enough from a file, and turn it into a function as per one of your blog posts, but I cannot then manage to implement the fnGetParameters("File Path") function to call the folder and files into the query.
Additional issue - and again can be solved outside of PQ but would be good to know - each workbook has different sheet names e.g. "2014" and "2015" etc for Sheet1 - the function query based on a Excel File used the name that was in the workbook used to create the query - how do you get PQ to ignore the Sheet name?
Sorry - I would have posted this in the forum, but there is not a section for Power Query.
Thanks again for all your great articles.
Happy Easter
David
OK - an update - I have managed to work around this issue by judicial filtering following import of the data - its a bit clunky as it would be better to remove the 20 lines of rubbish from the top of all SAP files on import as opposed from filtering out garbage following. Anyway - important thing is - its working..
Also - by re-reading and re-trying several versions of the folder import, I dont have to worry about the tab names... which is handy.
Sorry for the false alarm. and thanks again for a very inspirational blog.
David
Ken - thanks so much for this - excellent. It has really helped with a problem for one of my clients and their reporting needs!
KJ
Glad to help!
Hello Ken,
I am new at PQ functions.
I am trying to add choosing a folder to a query that is loading a csv file. top line with hard-coded path works. I am using your UpdatePQ and BrowseForFolder.
= Csv.Document(File.Contents("C:\Users\SMITH_MT\Documents\Clients\TDR\Client___TRANSACTION_REPORT_040817.txt"),[Delimiter=";",Encoding=1252])
However, when I replace the folder path with the function as below I get this error, "Expression.Error: The path 'I:\My Files and Documents\Work\Excel stuff\Client work in progress\TDR' is a folder path. A file path is expected."
I understand that this means I need to choose a file instead of a folder. However, I don't know how to do that. Can you help?
= Csv.Document(File.Contents(fnGetParameter("File Path")),[Delimiter=";",Encoding=1252])
Hey Mt,
You need to re-evaluate your code to Get Data --> From Folder. You'll then be able to pass the path in. This will allow you to further drill down and deal with the file(s) in that folder.
Hope that helps!
Hi Ken,
Thank you, it worked.
I added a new function below and called it instead of the BrowseForFolder function from your original file.
Function BrowseForFile() As Variant
BrowseForFile = Application.GetOpenFilename(, , "Choose data file.")
End Function
Thanks again.
Matt
Ken,
Next, I'm trying to gather a group of files from a folder. However, if there are extra files, I want to ignore them.
So, I want
Client A.txt
Client B.txt
Client C.xlsx
but not Client D.txt
I'm going to try to use a parameter table to do that.
Also, I'm wondering if I could use power query and VBA to clean and feed a bunch of csv files to Access.
I'll let you know how it goes.
Matt
Good luck with it Matt. Just on the "feed to Access"... you'll need to use VBA to push them. Power Query only lands data in Excel or Power BI unfortunately, and doesn't have a Push to Other App feature.
Hi Ken - I've employed your solution and it works great on my local machine, but when my coworkers try to use the file they get a run time error at the point in the code where it addresses the bail out command: wsData.Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
Any tips on how to ensure this works for other users besides the author?
Joy, this code shouldn't be specific to an author. It leads me to believer that maybe the table wasn't in the workbook, or the VBA code is missing something...
Hi Ken,
Really thank for sharing this. I am using it almost in daily basis. It would be awesome though if you could use the msoFileDialogFolderPicker instead of the ShellApp and set as default folder in the file browser the folder the current excel is in.
Thanks again