Extracting the specific date from the filename in Power Query.

Hi littlevillage 

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