Extracting the specific date from the filename in Power Query.

[small_village]

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.

[#2 · Accepted solution]

Hi @small_village 

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

[#3]

@L z. 

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

[#4]

@small_village 

Assuming I understood, the following should do it:

Date.FromText(Date.ToText(DateTime.Date(DateTime.LocalNow()), [Format="yyyy.MM."]) & Text.Combine(NoNull))

[#5]

@L z. 

Thank you again for help.

Regards,

Tuan.

[#6]

Glad I could help & Thanks for providing feedback