cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

 

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
PattemManohar
Community Champion
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. 

 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

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

 

image.png

 

If there is any hurdle in modifying your M-code, please feel free to post the same I'll modify it accordingly.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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

 

Picture1.gif

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.