Appending files and applying dates to files without dates

Question

Every day we are to download the current balances in excel form and copy them into a different sheet. The excel files are relatively simple and look like the current state shown below. I would like to query and append these files into one large table, but the files do not include a date column and there is no way to add that into the export. Is there anyway to automatically add the date the file was retrieved into it's own column for each of these files?

I would think if there was a date/timestamp in the filename that might help, but for some reason only my coworker's files download from the bank with those stamps, mine do not. So can someone please provide a solution for both scenarios?

Thank you for the help and let me know if I can provide additional information

Current state:

Entity	Balance 1	Balance 2
Account 1	100,000	200,000
Account 2	200,000	250,000

desired state:

Date	Entity	Balance 1	Balance 2
10/24/2022	Account 1	100,000	200,000
10/24/2022	Account 2	200,000	250,000

antrikshsharma · Answer

Since there is no trace of original time of export you can only rely on the Last Created Date of the file.let
    // Have a sepcific folder where you only download those files                                                             
    Source = Folder.Contents ( "C:\Users\SharmaAnt\Downloads" ),
    FilteredRows = 
        Table.SelectRows (
            Source,
            each List.Contains ( { ".xlsx", ".xls", ".xlsm", ".xlsb" }, [Extension] )
        ),
    // Identify Max Date                    
    MaxDate = List.Max ( FilteredRows[Date created] ),
    Result = Table.SelectRows ( FilteredRows, each [Date created] = MaxDate )
in
    Result&nbsp;

anonymous · Answer

thank you&nbsp;AntrikshSharma&nbsp; I will investigate this solution.&nbsp; Is there a way to create a column based off file name?&nbsp; For example, if I relabled a file as 10.24.22 could I use that file name to then create a column in the data that just shows 10/24/2022?

antrikshsharma · Answer

Yes, one of the columns returned by the above query is the name of files in that folder, you can filter by that as well.

Forum Discussion

Appending files and applying dates to files without dates

3 Replies

Related content

Dataflow - Append

Appending Data

Group By not continuously applying

Incremental refresh appending not replacing

Append table kpi's