new column from import file timestamp

[Anonymous]

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

[PattemManohar]

@Anonymous Please try using "Get Data" from "Folder" option, then you can see all the metadata associated with the files in that folder. 

[Anonymous]

@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.

[Anonymous]

@PattemManohar

I have managed to bring in the actual file contents but only the Source.name appears as a column in my data. I opend the advance editor, and boy does it look cryptic.

it contains the following

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"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from _Leads_opportunities"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other 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"

My table looks like the following

[PattemManohar · Accepted solution]

@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"

[Anonymous]

@PattemManohar, top man... works now. many thanks