cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
appeljr Frequent Visitor
Frequent Visitor

Extract text with variations in text and delimiters

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 - personalTitlePublisher/yearSubject
9Duché, 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.
10Duché, 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.
11Duché, 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.
12Duché, 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Extract text with variations in text and delimiters

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"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

4 REPLIES 4
Super User
Super User

Re: Extract text with variations in text and delimiters

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.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




appeljr Frequent Visitor
Frequent Visitor

Re: Extract text with variations in text and delimiters

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 Frequent Visitor
Frequent Visitor

Re: Extract text with variations in text and delimiters

@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!

Super User
Super User

Re: Extract text with variations in text and delimiters

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"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 797 guests
Please welcome our newest community members: