cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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




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!

Highlighted
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




Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 431 members 4,092 guests
Please welcome our newest community members: