Forum Discussion

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

Use a field as a delimiter?

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!

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    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's avatar
    Thingsclump
    4 years ago

    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 

4 Replies

  • Thingsclump's avatar
    Thingsclump
    Icon for Resolver V rankResolver V
    4 years ago

    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 

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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"