Is it possible to import parameterised .csvs from a folder? Query not possible with File.Contents!

[mucksavage]

Hi all

first post here...I'm very much a noob so I apologise in advance if my query is overly basic. I've spent the last couple of days searching online for a solution but can't seem to find one.   

The objective; import and append a dynamic set of .csv files (Identically formatted) from a folder without first having to load all files from the folder first.  

To explain a little further...we receive a set of 7 files every day from a vendor via ftp into a folder which already contains all the historical files. The files are easily distinguishable as they contain a date.   I wish to extract the new files and append them into a single file.  Now, if I was certain that the number of files per day in future would always be 7 I could set up individual data queries and append later.  However, that is not the case.  

What I tried with regard to parameters etc. is below...I based this from a post from @ImkeF where she scraped information from a number of webpages.  however, the solution doesn't work in this instance because query functionality is not available with File.Contents (BIAccountant's version used web.contents).

FolderPath =  parameter for the folder containing the files. No problem.

                  = \\drive\importdata\skew\

ImportDataFiles = parameter containing the static part of the filenames, linked to the dynamic range

                           = USD_Skew_Vol_ICAP_DATA_

FileDate = the date portion of the filename. I separated this as it changes each day.. it simplified the ImportDataFiles parameter

               = 20211124.csv

So a complete filepath = FolderPath & ImportDataFiles & FileDate

I then tried to create a query to retrive the files;

FilePath = Excel.CurrentWorkbook(){[Name="OLFImport_FilePath"]}[Content]{0}[Column1],
FileDate = Excel.CurrentWorkbook(){[Name="ImportDate"]}[Content]{0}[Column1],
Source = Csv.Document(File.Contents(FilePath & ImportDataFiles & FileDate,
[Query = FilePath & ImportDataFiles & FileDate]),Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
in
#"Promoted Headers"

Is there an alternative method to achieve what I'm trying to do here?  I know that one could just organise that the files dump into new subfolders each day BTW lol, but that would be cheating!

Many Thanks
Neil

[Anonymous]

Hi @mucksavage ,

You can try dynamic filter using parameters, there's a video for reference: Dynamic Filter Using Parameters in PowerBI - YouTube

Also having an article for reference: Deep Dive into Query Parameters and Power BI Templates | Blog rozwiązania Microsoft Power BI | Micro...

Hope it helps you.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Good.  If not already, you can create your full string with dynamic data (previous day) in your custom column with this.  Adapt it as needed.

File.Contents("\\folder\subfolder\" & [ImportDataFiles] &"_" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -1), "yyyyMMdd") & ".csv")

Pat

[mahoneypat]

If you can predict the full filenames, you could dynamically generate them in your query as a single-column table, and then make a custom column with File.Contents and concatenate the new filenames with the folder path.  Can you give an example of a few full filenames?  Are they predictable?  Or have random characters?

Pat

[mucksavage]

Hi @mahoneypat 

sure thing...yep, the filenames are static bar the date so they are easy to define.  I've created (I think) a table as you have suggested with the filepath and filenames....