Forum Discussion
Solved
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 y...
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"
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
Community Champion
Community ChampionSorry 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"