The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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
Solved! Go to 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"
Proud to be a PBI 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.
Proud to be a PBI Community Champion
@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....
If there is any hurdle in modifying your M-code, please feel free to post the same I'll modify it accordingly.
Proud to be a PBI Community Champion
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
@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"
Proud to be a PBI Community Champion
User | Count |
---|---|
158 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |