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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors