Power Query Refresh Prompt To Select File

[wsnyder]

Hi,

Using M365.

A new data file is created daily. in .xlsx format.
How can I setup power query to always prompt user to select a file?

I was able to create the connection to a csv file using Legacy Wizard from Text and this is always prompting me to chooses a csv file
But I cannot seem to repeat when using xlsx file as my data source.

Thanks
-w

 

[GraH]

You can't. At least: I would not know how using any of the available Power Query features. Maybe with some VBA magic there is a way.

However you can inside Power Query read data from folder and retrieve file names. Then create a list from this result.
Use a parameter that can hold any of these values (being file names).
The end users clicks on the parameter query and is prompted to select a file from the given list.
Then end user needs to close the Power Query window.
Finally refresh the result query.

It's almost the same thing, but it does not offer the same user experience as with the legacy import.

 

[wsnyder]

Thanks GraH,

I'll take your workaround for a spin.
Thanks!
-w

 

[wsnyder]

I think I found a welcome solution on this one.
I imported all files from a folder, sorted on created date, chose to keep only the first row, expanded the columns, chose only the columns I needed to load.
I need to test some more but seems to be working correctly and easier than passing a parameter from the worksheet.

-w

 

[GraH]

Alternative is to filter on latest file on the column creation date. No need to sort first.

 

[Alex Blakenburg]

If you want to do it via VBA and have 30 mins to watch a video.
Celia Alves (Solve & Excel) covers it in this video
How to add a File Picker in Excel to select the data source file for Power Query – T0019

 

[wsnyder]

Alternative is to filter on latest file on the column creation date. No need to sort first.

Thanks GraH,

Perfect!

Thanks
-w