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

J

jajatidev

Board Regular
Joined
Jul 29, 2016
Messages
78
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
If the report names are always consistent like in the sample data, this should do it. You can wrap the formula with DATEVALUE() to convert it to an actual date.

EXCEL
AB
1Source.NameDate
2Direct SJ3 +Global Tier-1 order Unclean report-01-09-2023.xlsx01-09-2023
3Direct SJ3 +Global Tier-1 order Unclean report-02-09-2023.xlsx02-09-2023
4Direct SJ3 +Global Tier-1 order Unclean report-03-09-2023.xlsx03-09-2023
5Direct SJ3 +Global Tier-1 order Unclean report-04-09-2023.xlsx04-09-2023
6Direct SJ3 +Global Tier-1 order Unclean report-05-09-2023.xlsx05-09-2023
7Direct SJ3 +Global Tier-1 order Unclean report-06-09-2023.xlsx06-09-2023
8Direct SJ3 +Global Tier-1 order Unclean report-07-09-2023.xlsx07-09-2023
9Direct SJ3 +Global Tier-1 order Unclean report-08-09-2023.xlsx08-09-2023
10Direct SJ3 +Global Tier-1 order Unclean report-09-09-2023.xlsx09-09-2023
11Direct SJ3 +Global Tier-1 order Unclean report-10-09-2023.xlsx10-09-2023
12Direct SJ3 +Global Tier-1 order Unclean report-11-09-2023.xlsx11-09-2023
13Direct SJ3 +Global Tier-1 order Unclean report-12-09-2023.xlsx12-09-2023
14Direct SJ3 +Global Tier-1 order Unclean report-13-09-2023.xlsx13-09-2023
15Direct SJ3 +Global Tier-1 order Unclean report-14-09-2023.xlsx14-09-2023
16Direct SJ3 +Global Tier-1 order Unclean report-15-09-2023.xlsx15-09-2023
17Direct SJ3 +Global Tier-1 order Unclean report-16-09-2023.xlsx16-09-2023
18Direct SJ3 +Global Tier-1 order Unclean report-17-09-2023.xlsx17-09-2023
19Direct SJ3 +Global Tier-1 order Unclean report-18-09-2023.xlsx18-09-2023
20Direct SJ3 +Global Tier-1 order Unclean report-19-09-2023.xlsx19-09-2023
21Direct SJ3 +Global Tier-1 order Unclean report-20-09-2023.xlsx20-09-2023
22Direct SJ3 +Global Tier-1 order Unclean report-21-09-2023.xlsx21-09-2023
23Direct SJ3 +Global Tier-1 order Unclean report-22-09-2023.xlsx22-09-2023
24Direct SJ3 +Global Tier-1 order Unclean report-23-09-2023.xlsx23-09-2023
Sheet6
Cell Formulas
RangeFormula
B2:B24B2=LEFT(RIGHT(A2,15),10)
 
Upvote 0
Just realized this is probably for PowerQuery. How about.

Power Query: 
= Table.AddColumn(Source, "Custom", each Text.Middle([Source.Name],47,10))
 
Upvote 0
In case the beginning of the text is not always in the same length, but the date is always at the end of the string, then the following could be also used.

Power Query: 
ReportDate = Table.AddColumn(Source, "Report Date", each Text.End(Text.Select([Source.Name],{"0".."9"} & {"-"}), 10))

With sample data:
Power Query: 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdI9CsJAEAbQqwxpdWVn1r/0gmCrViFFjFsEliSMKXJ8e2GE4bvAq17TVJdBc7/Q/ZZocy3Tqyv0GLIGpknfWek59iV3I2meJ11C5BDrIFHSbi2ftWq3fwjLEI9hIcmFWMreoRjEASeOOHHCiTNO1DDBESc8Rw3CU9QgPEENAt/J+E7GdzK+k/GdjO8UfKfgOwXfKb872y8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]),
    // Select only numbers and dashes, then take the last 10 chars as the date.
    ReportDate = Table.AddColumn(Source, "Report Date", each Text.End(Text.Select([Source.Name],{"0".."9"} & {"-"}), 10))
in
    ReportDate
1696367466288.png
 
Upvote 0
smozgur said:
In case the beginning of the text is not always in the same length, but the date is always at the end of the string, then the following could be also used.

Power Query: 
ReportDate = Table.AddColumn(Source, "Report Date", each Text.End(Text.Select([Source.Name],{"0".."9"} & {"-"}), 10))

With sample data:
Power Query: 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdI9CsJAEAbQqwxpdWVn1r/0gmCrViFFjFsEliSMKXJ8e2GE4bvAq17TVJdBc7/Q/ZZocy3Tqyv0GLIGpknfWek59iV3I2meJ11C5BDrIFHSbi2ftWq3fwjLEI9hIcmFWMreoRjEASeOOHHCiTNO1DDBESc8Rw3CU9QgPEENAt/J+E7GdzK+k/GdjO8UfKfgOwXfKb872y8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]),
    // Select only numbers and dashes, then take the last 10 chars as the date.
    ReportDate = Table.AddColumn(Source, "Report Date", each Text.End(Text.Select([Source.Name],{"0".."9"} & {"-"}), 10))
in
    ReportDate
View attachment 99673
Click to expand...
Thanks, Smozgur.
There is a small cath. Here is the actual data. I had removed some portion of the source name. The file name is consistent however the spacing is a concern.
Since the date format was consistent I used "Add column from example". However, I do need a better approach to extracting the date.

Source.Name
Direct SJ3 +Global Tier-1 order Unclean report-01-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-02-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-03-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-04-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-05-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-06-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-07-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-08-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-09-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-10-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-11-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-12-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-13-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-14-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-15-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-16-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-17-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-18-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-19-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-20-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-21-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-22-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
Direct SJ3 +Global Tier-1 order Unclean report-23-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx
 
Upvote 0
Still based on some assumptions. In the following approach, I assume the is only one opening parenthesis, so I can clean the part after the date.

Power Query: 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vdbLCsIwEIXhVxm6UjSSSby0S2lBEBTUumnpopcIhZhI7KKPrwt9ABHOKpvABwPzM2UZZX0w7UCXvabZzvqmtpT3JggmHzoT6Opaa2pHwTx8GIRkIROhpNIkaLI9Fu8n9femd6b7/CF/I12INDtR7To65OfpYrTPMarmv3Pqy0E0DdWW2FGusNway22wXIzlEijHErkHzFBNYUepsRy2KYxtCmObwtimcAzdA2xSlMRy2DNFYaOi/rhTqhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]),
    FirstPart = Table.AddColumn(Source, "FirstPart", each Text.Start([Source.Name], Text.PositionOf([Source.Name], "("))),
    ReportDate = Table.AddColumn(FirstPart, "Report Date", each Text.Start(Text.End(Text.Select([FirstPart],{"0".."9"} & {"-"}), 11), 10))
in
    ReportDate

I think it is possible to use Regex in Power BI desktop with a custom library. In that case, it is very easy to find the date pattern.

Also, if you can pre-clean the data by using some VBA, then you can use VBScript.RegExp library to extract date data, as shown in the basic sample below.

VBA Code: 
Sub extractDate()
Dim rng As Range
Dim cll As Range
Dim rgx As Object

    Set rgx = CreateObject("VBScript.RegExp")
    With rgx
        .Pattern = "\d{1,2}\-\d{1,2}\-\d{4}"
        .Global = False
    End With

    Set rng = Selection
    For Each cll In rng.Cells
        cll.Offset(, 1).Value = rgx.Execute(cll.Value)(0)
    Next cll
End Sub

Book1
AB
1Source.NameVBA Column
2Direct SJ3 +Global Tier-1 order Unclean report-01-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx01-09-2023
3Direct SJ3 +Global Tier-1 order Unclean report-02-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx02-09-2023
4Direct SJ3 +Global Tier-1 order Unclean report-03-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx03-09-2023
5Direct SJ3 +Global Tier-1 order Unclean report-04-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx04-09-2023
6Direct SJ3 +Global Tier-1 order Unclean report-05-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx05-09-2023
7Direct SJ3 +Global Tier-1 order Unclean report-06-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx06-09-2023
8Direct SJ3 +Global Tier-1 order Unclean report-07-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx07-09-2023
9Direct SJ3 +Global Tier-1 order Unclean report-08-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx08-09-2023
10Direct SJ3 +Global Tier-1 order Unclean report-09-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx09-09-2023
11Direct SJ3 +Global Tier-1 order Unclean report-10-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx10-09-2023
12Direct SJ3 +Global Tier-1 order Unclean report-11-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx11-09-2023
13Direct SJ3 +Global Tier-1 order Unclean report-12-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx12-09-2023
14Direct SJ3 +Global Tier-1 order Unclean report-13-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx13-09-2023
15Direct SJ3 +Global Tier-1 order Unclean report-14-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx14-09-2023
16Direct SJ3 +Global Tier-1 order Unclean report-15-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx15-09-2023
17Direct SJ3 +Global Tier-1 order Unclean report-16-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx16-09-2023
18Direct SJ3 +Global Tier-1 order Unclean report-17-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx17-09-2023
19Direct SJ3 +Global Tier-1 order Unclean report-18-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx18-09-2023
20Direct SJ3 +Global Tier-1 order Unclean report-19-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx19-09-2023
21Direct SJ3 +Global Tier-1 order Unclean report-20-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx20-09-2023
22Direct SJ3 +Global Tier-1 order Unclean report-21-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx21-09-2023
23Direct SJ3 +Global Tier-1 order Unclean report-22-09-2023 - (ANZ - Combined report of 3Z-CDQ and MTR).xlsx22-09-2023
24Direct SJ3 +Global Tier-1 order Unclean report-23-09-2023- (ANZ - Combined report of 3Z-CDQ and MTR).xlsx23-09-2023
Sheet1
 
Upvote 0
Using RegEx in PowerQuery

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RX = (x,y)=>
        let 
            Source = Web.Page(
                     "<script>var x='"&x&"';var y=new RegExp('"&y&"','g');var b=x.match(y);document.write(b);</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
 
Upvote 1
lrobbo314 said:
Using RegEx in PowerQuery

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RX = (x,y)=>
        let
            Source = Web.Page(
                     "<script>var x='"&x&"';var y=new RegExp('"&y&"','g');var b=x.match(y);document.write(b);</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
Click to expand...
Another beautiful day learning something new! (new to me, obviously!)

Thanks, @lrobbo314!
 
Upvote 0
And, if you are in Power BI, you can use Python to do it like this.

Power Query: 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vdbLCsIwEIXhVxm6UjSSSby0S2lBEBTUumnpopcIhZhI7KKPrwt9ABHOKpvABwPzM2UZZX0w7UCXvabZzvqmtpT3JggmHzoT6Opaa2pHwTx8GIRkIROhpNIkaLI9Fu8n9femd6b7/CF/I12INDtR7To65OfpYrTPMarmv3Pqy0E0DdWW2FGusNway22wXIzlEijHErkHzFBNYUepsRy2KYxtCmObwtimcAzdA2xSlMRy2DNFYaOi/rhTqhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]),
    Python_Script = Python.Execute("dataset['Date'] = dataset['Source.Name'].str.extract(pat='([0-9]{2}-[0-9]{2}-[0-9]{4})')",[dataset=Source]),
    Expand = Table.ExpandTableColumn(Python_Script, "Value", {"Source.Name", "Date"}, {"Source.Name", "Date"})
in
    Expand
 
Upvote 0
First time I see regex in PQ 🤯.... #learning.
Thanks for the python too....
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Power Query chose a value <=date
Replies
1
Views
208
AlienSx
A
J
  • Solved
Power Query IF Function combined with NETWORKDAYS.INTL
Replies
1
Views
165
alansidman
alansidman
J
  • Question Question
Power Query - Change column to row if columns match - Grouping
Replies
2
Views
648
Jessica553
J
J
  • Question Question
Excel Power Query & Power Pivot: Ageing Trend
Replies
1
Views
312
jajatidev
J
muhammad susanto
  • Question Question
Power BI: Calculate Count Formula From All Categories (Columns)
Replies
0
Views
240
muhammad susanto
muhammad susanto

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top