Extract the date to create a report date column from Source.Name column

[jajatidev]

Hi,
I have 15 Excel reports sourced from SharePoint.
The Source.Name column captures the report name which has the date mentioned.
Please advise how I can create a Report Date column that only captures the date from the Source.Name column so that I can hide the Source Name column before loading data for visualization.

Source.Name
Direct SJ3 +Global Tier-1 order Unclean report-01-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-02-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-03-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-04-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-05-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-06-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-07-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-08-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-09-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-10-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-11-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-12-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-13-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-14-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-15-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-16-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-17-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-18-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-19-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-20-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-21-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-22-09-2023.xlsx
Direct SJ3 +Global Tier-1 order Unclean report-23-09-2023.xlsx

 

[lrobbo314]

Thanks for the comments. I dig the fact that I can show some stuff you haven't seen before.

It has been a while since I played around with RegExp in PQ.

Playing around with it, here is a cleaner version.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RX = (str,exp)=>
        let
            Source = Web.Page(
                     "<script>document.write('" & str & "'.match('" & exp & "')[0]);</script>")
                     [Data]{0}[Children]{0}[Children]{1}[Text]{0}
        in
            Source,
    GetDate = Table.AddColumn(Source, "Date", each RX([Source.Name],"[0-9]{2}([\-/ \.])[0-9]{2}[\-/ \.][0-9]{4}"))
in
    GetDate