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 w...
Need Help
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"