Forum Discussion
Post ProdigyHow to get create file date in query
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.
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
10 Replies
- amitchandak
Super User
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/
- 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
Frixel try following these steps
- Connect the folder
- Combine and transform
- From the step's settings tab in the right side of your screen click on the gear of Removed columns' step
- 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
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.
- 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" 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.
