Extract text with variations in text and delimiters

[appeljr]

Dear @ImkeF ,

Is there a way to extract the date from the Publisher/year column from the into a new column, if there are variations thereof that include brackets, and where one can't assume that you can use last non-number delimiter? Sometimes the year has brackets, othertimes it doesn't, etc. Would something like last seven characters exluding a..z but allowing brackets and periods be a solution?

If it has brackets, I want them carried over, but if it doesn't, I don't want to add them.

Consider the variations in Publisher/year:

Record number :	Author - personal	Title	Publisher/year	Subject
9	Duché, Jacob, 1738-1798.	Discourses on various subjects, by Jacob Duché, M. A. Rector of Christ-Church and St. Peter's, in Philadelphia; and Formerly of Clare-Hall, Cambridge. ...	Dublin: printed by B. Dugdale, No. 150, Capel Street, M.DCC.LXXXVII. [1787]-88.	Sermons, American.
10	Duché, Jacob, 1738-1798.	Discourses on various subjects, by Jacob Duché, M.A. Formerly Rector of Christ-Church and St. Peter's, in Philadelphia; And Late Chaplain to the Asylum for Female Orphans, in the Parish of Lambeth, Surry. The third edition. To which are added two discourses preached at the chapel of the asylum, now first published. ...	London: printed for T. Cadell, In The Strand, M.DCC.XC, 1790.	Sermons, American.
11	Duché, Jacob, 1738-1798.	The duty of standing fast in our spiritual and temporal liberties, a sermon, preached in Christ-Church, July 7th, 1775. Before the First Battalion of the city and liberties of Philadelphia; and now published at their request. By the Reverend Jacob Duché, M.A.	Philadelphia: Printed and sold by James Humphreys, Junior, the corner of Black-Horse Alley, Front-Street, M,DCC,LXXV. [1775]	United States -- History -- Revolution, 1775-1783.
12	Duché, Jacob, 1738-1798.	The duty of standing fast in our spiritual and temporal liberties, a sermon, Preached in Christ-Church, July 7th, 1775. Before the First Battalion of the City and Liberties of Philadelphia; And published at their Request. By the Reverend Jacob Duche, M.A.	[London]: Philadelphia printed: London re-printed, and sold by T. Evans No. 54. Pater-Noster Row, MDCCLXXV. [1775]	United States -- History -- Revolution, 1775-1783. Sermons, American -- 18th century.

Thank you so very much for your help with the earlier solution!

[ImkeF]

Hi @appeljr ,

this formula extracts the last 4-digit-number from a string:

List.Select(
    Text.Split(
        Text.Combine(
            List.Transform(
                Text.ToList(<YourString>), 
                (l) => if List.Contains({"0".."9"}, l) 
                        then l 
                        else ","), 
            ""), 
        ","), 
    (l) => Text.Length(l) = 4)
    )

Where <YourString> is the reference to the string to grab from. So you might want to use this in an Add-Column command and replace it with a reference to the column.

To grab the square brackets as well, you can then add sth like: 

if Text.Contains(<YourString>, "[" & NumberResult & "]") then "[" & NumberResult & "]" else NumberResult

or so.

[appeljr]

Dear @ImkeF ,

I successfully got the 4-digit year, but couldn't figure out how to append the given code for selectively adding brackets. Here's what my current code looks like:

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Year", each List.Select(
    Text.Split(
        Text.Combine(
            List.Transform(
                Text.ToList([#"Publisher/year"]), 
                (l) => if List.Contains({"0".."9"}, l) 
                        then l 
                        else ","), 
            ""), 
        ","), 
    (l) => Text.Length(l) = 4)
    ),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Year", each Text.Combine(List.Transform(_, Text.From)), type text}),

Since NumberResult isn't in the M language documentation, I'm assuming I'm supposed to fill that in from some element of the above, but putting in "Year" in place of NumberResult generates an error (even when I've gotten the syntax to pass). 

Can you clarify? I'm still very new to this.

Thanks!

[appeljr]

@ImkeF I tried a few other things but I'm still stuck; any help you can offer with the above would be most welcome.

Thanks in advance!

[ImkeF · Accepted solution]

Sorry @appeljr ,

have been very busy.

Please check the following code (paste into the advanced editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZTPbtpAEMZfZcTZtiApMqEnIEWkoikKaYREOSz2EG+73nVn10E+9nH6HL32oTq7/EmitM2haU9gz+7MN99vxstl66wVtc7rrMAfXyN4KzKzjqCTnvbiTnrWS3xQ2szUZNGC0XAnSJragq3XnzBzNoJ1s7sGPsv3bxG8S2CQwBVHDYHZwKggaV08KmrKChA6h7lLYIYO6WPdbp+knERqmBVSiRxVVUjxOhwbGyqRVBOSKEEYT4RSEYxEuSaZ32ICSRIk1msldR8qktph7iUNE9ZzmwuFEVyaBDrdtr9YoeLqhOi8zvPRKJkuFoubi4sElp20l67iXmh6jlQazcIGrEBmQietVbRsddr/wC0269jpX7o24GNT4ZBvi0oJjjsDrkAY2EbVJWw49xhLdgXeU1UIvUviT8xYqi185Snbi66IYF4TNQlcc9QVknLAXDppNL8ysC2k10UIIs/Zc7c1kN83XxEKtikH4UL6rAjec3r/JIKcCLTZwkaSdVB5hJYvHJhOjc7NA6Ze+nXCCLlZnoELHXQxS7bmwHIx8jTO2n8i2HmOoE+b1y5MnXWcXepb2AjWyE5xd2ArSdLVQgUqDsvKED8ouUZyErmkABuqR/c28N1HRLl2zbxT73MnTbsJDJFbxGDPOFgyFM4JxX4fXMskq/I1j6V85OneeFePfu4BSALCLzVaHqJhE9Jd4R0S8vmnA8k2PEzbh9megk9vjcp3c1yygkldVgVhY31HWhqKdloNaQyDPFQi+xxPDI8FDJTCJoIxGe3i4x5GzC7iPbwJS5h2V1z+g5a+3tzxNFuIY5iwd4Ya/5eFG1X7Sdx5x+h6p3u8J/8V7+wl8Y4OeKe/x+sX/Bdor55Hi0eyy91qrfqPch8WrQ+7MI9LvH8VPcLOS/jmjr8c4bPafcXfJEZE8aWx/ANXZsuFGOgL8Fz9BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Record number :" = _t, #"Author - personal" = _t, Title = _t, #"Publisher/year" = _t, Subject = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record number :", Int64.Type}, {"Author - personal", type text}, {"Title", type text}, {"Publisher/year", type text}, {"Subject", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LatestYear", each List.Last(List.Select(
    Text.Split(
        Text.Combine(
            List.Transform(
                Text.ToList([#"Publisher/year"]), 
                (l) => if List.Contains({"0".."9"}, l) 
                        then l 
                        else ","), 
            ""), 
        ","), 
    (l) => Text.Length(l) = 4))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each if Text.Contains([#"Publisher/year"], "[" & [LatestYear] & "]") then "[" & [LatestYear] & "]" else [LatestYear])
in
    #"Added Custom1"