Use a field as a delimiter?

[PaulDBrown]

Hi again,

Thanks to some PQ magic provided by generous contributors to this community (see this thread: Extract postal code ), I now need to "use" the extracted postal code as a "delimiter". In other words, I need the string for the location immediately after the postal code and the next comma.

See this sample data:

Address	Postal Code	Espected
Carr. Santillana, 3, 04510 Abla, Almería, España	04510	Abla
Calle Abrucena, 24, 04007 Almería, España	04007	Almería
Plaza Andalucia, 2, 04520 Abrucena, Almería, España	04520	Abrucena
Calle Vistillas, 3, 04500 Fiñana, Almería, España	04500	Fiñana
Calle Mirlo, 1, 04009 Almería, España	04009	Almería
Calle de San Sebastian, 11, 04500 Fiñana, Almería, España	04500	Fiñana
Calle Aguila, 3, 04459 Ohanes, Almería, España	04459	Ohanes
Calle Granada, 12, 04540 Nacimiento, Almería, España	04540	Nacimiento
Calle Real, 4, 04450 Canjáyar, Almería, España	04450	Canyámar
Calle Real, 2, 04458 Almócita, Almería, España	04458	Almócuta
Calle Camacha, 28, 04530 Doña Maria, Almería, España	04530	Doña María
Calle Ruiz Ocaña, 20, 04458 Padules, Almería, España	04458	Padules
Calle Canalejas, 10, BAJO, 04470 Laujar de Andarax, Almería, España	04470	Laujar de Andarax
Plaza Poeta Bernardo Martín del Rey, 2, 04460 Fondón, Almería, España	04460	Fondón
Calle Sebastián Pérez, 76, 04550 Gérgal, Almería, España	04550	Gérgal
Calle Granada, nº 12, 04479 Bayárcal, Almería, España	04479	Bayárcal
Av. de la Paz, 3, 04440 Rágol, Almería, España	04440	Rágol
Calle Sebastián Pérez, 7, 04550 Gérgal, Almería, España	04550	Gérgal
Plaza de la Constitución, 11, 04569 Terque, Almería, España	04569	Terque
Calle la Paz, 6, 04560 Gádor, Almería, España	04560	Gádor
Plaza Mayor, 2, 04479 Paterna del Río, Almería, España	04479	Paterna del Río
Calle Concha Ros, 3, 04430 Instinción, Almería, España	04430	Instinción
Av. Principe de Asturias, Nº 28, 04431 Íllar, Almería, España	04431	Íllar
10, Plaza Nueva, 04409 Alicún, Almería, España	04409	Alicún
04568 Alsodux, Almería, España	04568	Alsodux
Calle Carrera del Carmen, 14D, 04440 Rágol, Almería, España	04440	Rágol
Carr. Almeria de Cherin, S/N, 18480 Ugíjar, Granada, España	18480	Ugíjar
Calle Abrucena, 24, 04007 Almería, España	04007	Almería

The issue is that the location can be after the first, second or sometimes even third comma from the right of the string, but it is always after the postal code (so this is the ideal "delimiter")

Any help would be hugely appreciated!

[CNENFRNL · Accepted solution]

Regex magic once again

let
    RE = (regex as text, str as text) =>
        let
            html = "<script>var regex = " & regex & "; var str = """ & str & """; var res = regex.exec(str); document.write(res[0])</script>",
            res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
        in res,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVW7bttAEPyVhWvBOUq0HiUlJ0aC6AE5SWO4WJMH6YTT0TmSRqR/yEeoTKEicOcy92PZ5YlGipBU0h3JnX3MzB7v7i4maO0l3KLJldZosAO9DojwKhAQPWh6jPRWWnek09vsEd1PvLjvMExrSRG2iCWDuiGjhBjUxC807hEik6AuYsWAskpX/JGjqdIXlZUNZlV/QsA7RTFtwKmyOu1A4NsbNcYmkomAW/mAVA0NwYJ/KhatCqUrCsOrEczXaGTWDLqxlDehT4HnJBQww1htlTR52gxdStQdCH01ARM0G3fYoT0H1fWoIYe651jlLbNNcIvxmpUblm32BFynFAJTtKoFuyzUHuYxvyW8qCovMCl0Gz00FGq5YeUDQo6jD/MSPxDwEYsNWpaNjWXxW10m775FKnOEsbQGbZJy37k7GoJromRXMdInrVOTuGfT3NfJJe5gYOF+WLnvwKBfMkNK3NCbFbN8lvDm18tJ/HAwgjHu3MHG9ejo6ZJn1jQR7iuzkWuW7rBKW2r+reszm/Yk+sKT1FCWnFa55Om0Jv0RfJL2ayGbe6ga92z1ufAhSWtN6+tOccchrywtMGchvXru2LIo1C95F5ZpdX+EZN/3PIM5jVBP9cJy0GN5PURZXpDbKcuMNPObEPYCcN/pbqqdgH3rp5gV8glLUHkVqdi91NZmbng7szQpap1d7Yi10noy6LyVrEl4fa4x+A9QflOlvpM1nSjD7ZsZpRmGQwGfV+644QFfPfvfP4P73w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),

    #"Added Regex" = Table.AddColumn(Source, "Regex", each RE("/\b\d{5} .*?(?=,)/gi", [Address])),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Regex", "Regex", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Postal Code", "City"})
in
    #"Split Column by Delimiter"

[Thingsclump · Accepted solution]

Hi @PaulDBrown 

Here is an another solution.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVbBctowEP0VDWdPKoMxcHRIm2mnIQxpe8nksLE1YEbIqbAzhX/oR3DsgUMntxyrH+uuZBMnqdKZtCcZ/PR4+/atxOVlZwxaH7ELUGUuJSgIWC9gPOqHnCXXEj8mciW02ePT2/UNmJ/QCTr2Pa6E6FwFxCKlwA26SgVxdCMi4Xzg2Y5vaHv9zlJMJWyBJSoDWaU5cVgdXd6i9WjpOi0O1dLzJV/bqtZNUZyzdzlue4GLE1cNalGd5VoWAQtdWSNvWaOnZbntmSCL2YW4BpQECpnCf1SUzKtcNu2K+iN2vgAl1j4aRODqMC2WU42sGWJDZ3fE2QTSfJULVRZeSRFJesC1+GYCZMAip4mzMail2W1A+2URFcI2ZrcC/Yyp65iGtN3cpXnp9QlBznuEVWXbqTGsIF1Qooa2xh5nJwVuY2egc7/vPVJ2AD7u56zKt+w8JTDS8kbkFLJKvtQDkliDHglUIMWSchoi13Hy4dwyDjj7CNUSNOWHJkPDNy/3gOQ+g7dGa1qIEtix0Ap0VlBNpdkrxEr0etNYHWMgC5WZO+X9pdgG0oFaVdThNjvFpuaHFtuADWJrOAbhFL+ZU0M9ZtsY1KA/xVP9uq8jGg1G7BgwLjr18yEI1wPMMia3R2SMRCdg28wN5n1mdvPCz2Sz7jB/KfbVtbr+OG3jQiFviUegbUF9TMQj9knor5XwksZUsMO0ZDbFuj7EpG2XFd5p7MdOGmFays5gQ3sO9k+hpBS56Ji995xwXXiKbge/UDiYbFY0J3SEs/meDFB1/R5iO5st4KHBU03f3NgTN1mXFQ44ck8wPW74o17IzHe8E/wnUi/E1WEsLY2k82FSiVuwLPYGyFNz75dYXwUWZHnIXjrH1kVWeccYIXabBT06IrQW2pmIzytB2YhOXp1huvQtOLfBGy/wCSkv3kyQdxgNOfs8N/sl2XSYwQdKi8C1xvyXvwBXvwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t, #"Postal Code" = _t, Espected = _t]),
#"Added Custom" = Table.AddColumn(Source, "City", each Text.BeforeDelimiter(Text.AfterDelimiter([Address],[Postal Code]),","))
in
#"Added Custom"

Thanks

Thingsclump

www.thingsclump.com 

[PaulDBrown]

@Thingsclump 

Excellent! Nice and simple! Thank you!

[CNENFRNL · Accepted solution]

Regex magic once again

let
    RE = (regex as text, str as text) =>
        let
            html = "<script>var regex = " & regex & "; var str = """ & str & """; var res = regex.exec(str); document.write(res[0])</script>",
            res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
        in res,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVW7bttAEPyVhWvBOUq0HiUlJ0aC6AE5SWO4WJMH6YTT0TmSRqR/yEeoTKEicOcy92PZ5YlGipBU0h3JnX3MzB7v7i4maO0l3KLJldZosAO9DojwKhAQPWh6jPRWWnek09vsEd1PvLjvMExrSRG2iCWDuiGjhBjUxC807hEik6AuYsWAskpX/JGjqdIXlZUNZlV/QsA7RTFtwKmyOu1A4NsbNcYmkomAW/mAVA0NwYJ/KhatCqUrCsOrEczXaGTWDLqxlDehT4HnJBQww1htlTR52gxdStQdCH01ARM0G3fYoT0H1fWoIYe651jlLbNNcIvxmpUblm32BFynFAJTtKoFuyzUHuYxvyW8qCovMCl0Gz00FGq5YeUDQo6jD/MSPxDwEYsNWpaNjWXxW10m775FKnOEsbQGbZJy37k7GoJromRXMdInrVOTuGfT3NfJJe5gYOF+WLnvwKBfMkNK3NCbFbN8lvDm18tJ/HAwgjHu3MHG9ejo6ZJn1jQR7iuzkWuW7rBKW2r+reszm/Yk+sKT1FCWnFa55Om0Jv0RfJL2ayGbe6ga92z1ufAhSWtN6+tOccchrywtMGchvXru2LIo1C95F5ZpdX+EZN/3PIM5jVBP9cJy0GN5PURZXpDbKcuMNPObEPYCcN/pbqqdgH3rp5gV8glLUHkVqdi91NZmbng7szQpap1d7Yi10noy6LyVrEl4fa4x+A9QflOlvpM1nSjD7ZsZpRmGQwGfV+644QFfPfvfP4P73w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),

    #"Added Regex" = Table.AddColumn(Source, "Regex", each RE("/\b\d{5} .*?(?=,)/gi", [Address])),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Regex", "Regex", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Postal Code", "City"})
in
    #"Split Column by Delimiter"

[PaulDBrown]

@CNENFRNL 

Perfect!! Thank you so much!! (I really need to learn this stuff....)