Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I need PowerQuery to go every time and search for a file that is stored in a folder with today's date.
Explanation:
Our raw data (the one we feed to PowerQuery) are stored to our local shared drive; this is an example of the path:
REPORTS [FOLDER]
----------30102019 [FOLDER]
file1.csv
----------31102019 [FOLDER]
file1.csv
As you can see, every day the new report is stored in a folder with the same date of when the report is extracted.
How can I tell PowerQuery to go and look for the latest folder and pickup file1.csv?
Solved! Go to Solution.
Hi luke_avalle,
You could try below M code to see whether it work or not
let
Source = Folder.Files("C:\Users\(username)\Documents\New folder"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "C:\Users\zoezhi\Documents\New folder\"&Date.ToText(DateTime.Date(DateTime.LocalNow()),"yyyyMMdd")&"\"))
in
#"Filtered Rows"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi all,
Just to add an extra bit of complexity onto this - how would I allow other users to refresh this power query?.
Let's say I have a sharepoint with the excel file in. Is it possible for others who have access to this sharepoint to go in and refresh this query so it pulls the most recent data, without them having to setup a new data source connection each time?
Hi luke_avalle,
You could try below M code to see whether it work or not
let
Source = Folder.Files("C:\Users\(username)\Documents\New folder"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "C:\Users\zoezhi\Documents\New folder\"&Date.ToText(DateTime.Date(DateTime.LocalNow()),"yyyyMMdd")&"\"))
in
#"Filtered Rows"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous I had something similar that I had to work with. The solution I found was by using a the
DateTime.LocalNow()
function in Power Query. You can then extract the day, month, and year and create a string to match the format that you use in your file structure. To manipulate the date, you can use
DateTime.ToText()
function to format the date as you wish.
Hope that helps!
Hi @Anonymous ,
We need to get @KenPuls who wrote an incredible book, M is for (DATA) MONKEY if he is available to comment.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!