Forum Discussion

Mikey575's avatar
Mikey575
Frequent Visitor
2 years ago

Importing Specific csv files from a folder.

I want to import specific .csv files from a folder and need it to be dynamic so i dont have to move any files or change the name file. I also need the dates to dyamically move on each month so once we get past a quarter end it picks up the file with that date.

 

The files have a date at the end of their title so can be differenciated by that. They are monthly files (dated last day of month) but i only want to import ones that have a Quarter end. Removing the files that arent needed from the folder isnt an option.

 

So in the list below of all the files in the folder i only want to import the ones with the dates in the title of 20230331, 20230630, 20230930:

 

CompanyAUMData_20230131.csv

CompanyAUMData_20230231.csv

CompanyAUMData_20230331.csv

CompanyAUMData_20230430.csv

CompanyAUMData_20230531.csv

CompanyAUMData_20230630.csv

CompanyAUMData_20230731.csv

CompanyAUMData_20230831.csv

CompanyAUMData_20230930.csv

CompanyAUMData_20231031.csv

 

I am guessing it would mean the use of parameters but am happy to try any method.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Mikey575 - Yes there is.  If you connect to the excel file using the File.Contents - PowerQuery M | Microsoft Learn or SharePoint.Files - PowerQuery M | Microsoft Learn you will be provided with all the Excel files in the selected folder path.  This is presented in a standard table, so you can apply transformations to filename.   This will allow you to find add a column with Date of the file.  You can then apply any filters to select files.

    • Mikey575's avatar
      Mikey575
      Frequent Visitor
      2 years ago

      Anonymous Thank you for your respose Daryl. I could get what you suggested to work but i can see its on the right lines. Please could you give me some more detail?

  • ERD's avatar
    ERD
    Community Champion
    2 years ago

    Mikey575 , you might use a Date table in Power Query and via adding/removing extra columns achieve the result. I just took the names, but the logic is similar, just copy/paste and go through steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyzs8tSMyrdAz1dUksSYw3MjAyNjAystBLLi4Dy+NUZGxsSFiRibEBYUWmxJhkRoxJ5sSYZEGMIksirDM0gJkUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CompanyAUMData_20230131.csv = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([CompanyAUMData_20230131.csv] <> "")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "dt", each [CompanyAUMData_20230131.csv]),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Added Custom", {{"dt", each Text.AfterDelimiter(_, "_"), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"dt", each Text.BeforeDelimiter(_, "."), type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter", {{"dt", type date}}, "en-CH"),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type with Locale", {"dt"}, Date, {"Date"}, "Date", JoinKind.LeftOuter),
    #"Expanded Date" = Table.ExpandTableColumn(#"Merged Queries", "Date", {"End of Quarter"}, {"Date.End of Quarter"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Date", "check", each [dt] = [Date.End of Quarter]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([check] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"dt", "Date.End of Quarter", "check"})
in
    #"Removed Columns"