Forum Discussion
new column from import file timestamp
Hi
I have import data in XLS files. every week a new XLS is stored in the directory where powerbi reads data from.
what I would like to do is, when a new xls file is added and the records of its contents are added to my data table, I would want the created timestamp of the XLS file to be added in a column so that I can know when the said record was created.
BR
Chronis
Anonymous Please try replacing your code with below (You didn't remove the step I've mentioned to remove). Anyway, here it is. Hopefully it should work.
let Source = Folder.Files("E:\Docs\MyDocs\__One Drive IQT\OneDrive - INFO QUEST TECHNOLOGIES SA\_Leads_opportunities\Leads"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from _Leads_opportunities", each #"Transform File from _Leads_opportunities"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Renamed Columns1", "Transform File from _Leads_opportunities", Table.ColumnNames(#"Transform File from _Leads_opportunities"(#"Sample File"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"(Do Not Modify) Lead", type text}, {"(Do Not Modify) Row Checksum", type text}, {"(Do Not Modify) Modified On", type datetime}, {"Created On", type datetime}, {"Topic", type text}, {"Owner", type text}, {" Name", type text}, {"First Name", type text}, {"Middle Name", type any}, {"Last Name", type text}, {"Company Name", type text}, {"City", type text}, {"Description", type text}, {"Status Reason", type text}, {"Business Phone", type text}, {"Mobile Phone", type text}, {"Email", type text}, {"Lead Source", type text}, {"Job Title", type text}}), #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Source.Name], 4), type text), #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([Source.Name], 5, 2), type text), #"Inserted Text Range1" = Table.AddColumn(#"Inserted Text Range", "Text Range.1", each Text.Middle([Source.Name], 8, 2), type text), #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Range1",{{"First Characters", "Year"}, {"Text Range", "Month"}, {"Text Range.1", "Date"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", type number}, {"Month", type number}, {"Date", type number}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Date", "Day"}}) in #"Renamed Columns2"
6 Replies
- PattemManohar
Community Champion
Anonymous Please try using "Get Data" from "Folder" option, then you can see all the metadata associated with the files in that folder.
PattemManohar thanks, I did
Get data -> File -> Folder
and I got a table like yours. however, now, how do I import the actual data in the xls files?
- PattemManohar
Anonymous Good, then you need to expand the "Content" field. Then it will display actual data of your file.
Goto "Advanced Editor", modify the M-Code as below (Remove the below similar line in your M-Code).
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from FolderTest"}),Now you can see, I've metadata associated with the file as well as the actual data in the file. You can then, keep/remove fields as you like....
If there is any hurdle in modifying your M-code, please feel free to post the same I'll modify it accordingly.