Forum Discussion
Solved
Extracting the specific date from the filename in Power Query.
Hi,
I have three files that are named as "Day_1", "2 Day" and "Day3". The number 1, 2, 3 in three filenames respectively as the first, second and third day of months.
I need to create a table that includes a new column, it contains date values as (01.10.2022, 02.10.2022, 03.10.2022) and other value columns respectively.
I used "From Folder" to get datas.
I have added the expected result image and the link of samplefile as below:
https://drive.google.com/drive/folders/1FdxTlcGevP9bRv07Ug-eRaQBYsuShgtA?usp=sharing
Hope for your helps.
Thank you.
Adjust the Source step with your folder path:
let Source = Folder.Files("D:\Lorenzo\Downloads\SmallVillageFolder"), NoHiddenFile = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), SelectedColumns = Table.SelectColumns(NoHiddenFile,{"Content", "Name", "Extension"}), AddedDate = Table.AddColumn(SelectedColumns, "Date", each let ActualFileName = Text.BeforeDelimiter([Name],[Extension]), Split = Text.ToList(ActualFileName), TryDigit = List.Transform(Split, each try Text.From(Number.From(_)) otherwise null), NoNull = List.RemoveNulls(TryDigit) in Date.FromText("2022.10." & Text.Combine(NoNull)), type date ), RemovedOtherColumns = Table.SelectColumns(AddedDate,{"Content", "Date"}), SortedDate = Table.Sort(RemovedOtherColumns,{{"Date", Order.Ascending}}), ExtractedTableOne = Table.AddColumn(SortedDate, "TableOne", each let WbookContent = Excel.Workbook([Content]), SelectedTable = Table.SelectRows(WbookContent, each ([Name]="Table1") and ([Kind]="Table")) in Table.First(SelectedTable)[Data], type table ), RemovedContent = Table.SelectColumns(ExtractedTableOne,{"Date", "TableOne"}), ExpandedTableOne = Table.ExpandTableColumn(RemovedContent, "TableOne", {"Area", "ID", "Unit"}) in ExpandedTableOne
Adjust the Source step with your folder path:
let Source = Folder.Files("D:\Lorenzo\Downloads\SmallVillageFolder"), NoHiddenFile = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), SelectedColumns = Table.SelectColumns(NoHiddenFile,{"Content", "Name", "Extension"}), AddedDate = Table.AddColumn(SelectedColumns, "Date", each let ActualFileName = Text.BeforeDelimiter([Name],[Extension]), Split = Text.ToList(ActualFileName), TryDigit = List.Transform(Split, each try Text.From(Number.From(_)) otherwise null), NoNull = List.RemoveNulls(TryDigit) in Date.FromText("2022.10." & Text.Combine(NoNull)), type date ), RemovedOtherColumns = Table.SelectColumns(AddedDate,{"Content", "Date"}), SortedDate = Table.Sort(RemovedOtherColumns,{{"Date", Order.Ascending}}), ExtractedTableOne = Table.AddColumn(SortedDate, "TableOne", each let WbookContent = Excel.Workbook([Content]), SelectedTable = Table.SelectRows(WbookContent, each ([Name]="Table1") and ([Kind]="Table")) in Table.First(SelectedTable)[Data], type table ), RemovedContent = Table.SelectColumns(ExtractedTableOne,{"Date", "TableOne"}), ExpandedTableOne = Table.ExpandTableColumn(RemovedContent, "TableOne", {"Area", "ID", "Unit"}) in ExpandedTableOneThank you very much.
It's actually worked 100%.
In your approach, I just change slightly the formula:
Date.FromText(Date.FromText(DateTime.LocalNow(), [Format="yyyy.MM.dd"]) & Text.Combine(NoNull)),I expect it can return current date, my goal is when I download the data everyday, i do not need to save the filename like "01.10.2022", "02.10.2022", "03.10.2022".
But it's not worked.Can you give me some advices.
Regards,
Tuan.
Assuming I understood, the following should do it:
Date.FromText(Date.ToText(DateTime.Date(DateTime.LocalNow()), [Format="yyyy.MM."]) & Text.Combine(NoNull))
