Been searching all over for this one. I've found help with how to set up an Excel table to feed a dynamic file name into PQ as a source, but I want to set up a parameter as a query that references the same folder all the time with the file name changing upon download. Thought I had set this up correctly but the source is not refreshing with each latest version of this download.
My PQ process is set up as follows:
1. My firm's report data automatically downloads to my computer's Download folder as an Excel CSV file. The latest version of this file is what I'll use to refresh my existing workbook (WB) query (3-12 times a day). Sample file path and name: C:\Users\me_myFirm\Downloads\TradeDetails10_27_2023 4_23_43 PM.csv
2. A pre-existing connection-only query references my Download folder > filters the folder's Date Modified column for "is latest" > adds a column to concatenate [Folder Path]&[File Name]&[Extension] > remove other columns > converts this path to List. Identical text as the file above.
3. A pre-existing parameter is set up as query, using the connection-only query above. The parameter is called SourceParam.
4. I replaced the static file source location of my main WB query with the parameter above: = Csv.Document(File.Contents(SourceParam))
I don't get any errors after adding this parameter to the source step, and all the subsequent 20 steps work, but the 'latest' source file isn't being referenced. It's still referencing the older file from my original parameter set-up.
Is it something in the types: lists vs queries vs tables? Or maybe a privacy/security issue using the computer's Download folder? I'm OK with basic PQ set up and loading into Excel, but probably over my skis here. Any insight would be greatly appreciated.
Thank you!
-Rich
My PQ process is set up as follows:
1. My firm's report data automatically downloads to my computer's Download folder as an Excel CSV file. The latest version of this file is what I'll use to refresh my existing workbook (WB) query (3-12 times a day). Sample file path and name: C:\Users\me_myFirm\Downloads\TradeDetails10_27_2023 4_23_43 PM.csv
2. A pre-existing connection-only query references my Download folder > filters the folder's Date Modified column for "is latest" > adds a column to concatenate [Folder Path]&[File Name]&[Extension] > remove other columns > converts this path to List. Identical text as the file above.
3. A pre-existing parameter is set up as query, using the connection-only query above. The parameter is called SourceParam.
4. I replaced the static file source location of my main WB query with the parameter above: = Csv.Document(File.Contents(SourceParam))
I don't get any errors after adding this parameter to the source step, and all the subsequent 20 steps work, but the 'latest' source file isn't being referenced. It's still referencing the older file from my original parameter set-up.
Is it something in the types: lists vs queries vs tables? Or maybe a privacy/security issue using the computer's Download folder? I'm OK with basic PQ set up and loading into Excel, but probably over my skis here. Any insight would be greatly appreciated.
Thank you!
-Rich
