How to get create file date in query

[Frixel]

Hello,

How and when can I get the created date of a file in my query?
If I have a query multiple files in a folder that I combine with the function combine files how can I afterwards get the creation date of the files in my query.
This date is not in the files themselves.

[DataVitalizer]

Hi @Frixel 
Are you willing to retrieve the created date of each file from your folder into one column in the combined file?

Did it work ?  👌 Mark it as a solution to help spreading knowledge  👉 A kudos would be appreciated

[Frixel]

@DataVitalizer 

Yes, that is what i want.

[DataVitalizer]

@Frixel try following these steps

1. Connect the folder
2. Combine and transform
3. From the step's settings tab in the right side of your screen click on the gear of Removed columns' step
4. Check Created date

Go back to last step of step's settings tab and check your data once again

Did it work ?  👌 Mark it as a solution to help spreading knowledge 👍 A kudos would be appreciated

[Frixel]

Hi @DataVitalizer 

I found what yoy mean and in the desktop it is okay, but when i publish in the portal and there is a refrech/update because there is a new file in the folder it goes wrong.

This is what i do.

Then i get this colomn:

If i then go to last step of step's settings tab i see this:

i change the colomn name to:

When i refresh in the Desktop there is NO issue.

Then i save and publisch to portal service and there is a update(new file in folder) then i get after long waiting this error:

Error in datasource:

Expression.Error: <pii>The column '11-10-2021 12:10:18' of the table wasn't found.</pii>. <pii>11-10-2021 12:10:18</pii>. . The exception was raised by the IDbCommand interface. Table: ExportFile.

What do i wrong?

[Frixel]

Hi @DataVitalizer 

Where is step 3 (the settings tab) what you mean?

[DataVitalizer · Accepted solution]

Hi @Frixel 

You need to select the combined file in the left tab then check 'Removed columns' step in the right tab

Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

[Frixel]

@amitchandak 

I think you don`t understand me.

Each file in the folder does not have a datestamp in a colomn in the file.

For my report i have combine all that files and every day when there is a new file. In my report i will to filter on a specific day so i see only the report from that day. 

But i have no colomn to filter on that in the 'Fields' and i have also not a 'Field'  with the source name.

[Anonymous]

Hi,

I was facing the same issue and after some research the solution was quite straight forward.

What you need to do is after expanding your files just delete the step where it removed columns it should be just above expanded columns step and you will get all the feilds you had before expanding select the creation date or any other feild yo need and delete the rest of them.

Please mark as solved if it works for you.

[amitchandak]

@Frixel , I tried another way. I modified the code and added file created date

check step - #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File","Date created"}),

let
    Source = Folder.Files("C:\Users\Amit.Chandak\Google Drive\Kanerika\power bi\Data\csv\combine"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File","Date created"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Item Id", Int64.Type}, {"Name", type text}, {"Brand Id", Int64.Type}, {"Category Id", Int64.Type}, {"Sub Category Id", Int64.Type}, {"Brand", type text}, {"Category", type text}, {"Sub Category", type text}})
in
    #"Changed Type"

[amitchandak]

@Frixel , Add date like this blog in each table, and then append the files

https://www.enhansoft.com/how-to-add-the-last-refreshed-date-and-time-to-a-power-bi-report/

https://www.excelguru.ca/blog/2016/06/08/display-last-refreshed-date-in-power-bi/