Hello everyone. I'm trying to setup a "dynamic" Excel Power query. What do I mean with dynamic? I need it to be able to let me choose a different CSV with a Macro or some sort of easy update. Problem is I can't find this function, I can only link it to a single file.
Any help?
You need to use the link to folder option. Then you will need a parameter query via which you can choose the files you wish to use. Hopefully forum PQ guru Olly will be along soon - he will be able to guide you better than I.
Last edited by AliGW; 07-09-2019 at 04:29 AM. Reason: Typo
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Assuming you want to query one specific CSV file, chosen manually, then put your filename in a cell, and define a Name for this cell. You can now refer to this named range directly in Power Query.
For example, defining a named range called "MyFilename" for the cell containing the filepath / name:
Now you can manually update the filename in that cell, or use VBA to update it, then simply refresh your query.
Please Login or Register to view this content.
If you wanted to determine which CSV file to use, in a folder, based on criteria, then we could do that, too - but would need to better understand your requirement.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
I'd like to manually choose the CSV from the folder. Ideally, when I click on the refresh macro / query it prompts me with the folder of the CSVs and I select the correct one, which is uploaded and formatted using the rules of the query.
So, you could use a little VBA to choose the file and trigger the query refresh. Something like:
in the Workbook module.Please Login or Register to view this content.
My bad, I forgot to mention that I'd like to not use VBA because of company restriction (the file will be shared and opened by different people). Is there a native way to do this?
If you want a click > prompt user experience, then you'll need VBA.
If you want to avoid VBA, then simply manually enter the filename into the appropriate cell, then click Data > Refresh All.
If you want GUI to pick csv...
Why not just use Query Editor? Just double click on gear icon by "Source" step in Applied Steps.
This will launch CSV import menu with option to browse for file.
0.JPG
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Because I would have liked this to be user friendly cause it's going to be a company file. So, I hoped that power query had such an option by default. Thank you anyway.Originally Posted by CK76
? That's the default.
But PQ is really meant for Data analyst and BI people. End users shouldn't be touching editor or changing source(s).
If you want to make data source to be dynamic, and available for user interaction.
What you'll need to do is design model and / or data storage, that others access via PowerPivot Data model, using single connection to multiple source.
Though that will involve more than just PQ and Excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks