Hoy daremos una solución alternativa, con Power Query, al clásico problema de separar los nombres completos en: Nombre + Apellido 1 + Apellido 2
Hace ya unos cuantos años publiqué un artículo done, con una UDF de VBA para Excel, se daba solución a casi todas las posibilidades de nombres y apellidos compuestos. Puedes releerlo aquí... un artículo muy interesante!!
Hoy empleando una técnica similar, pero con Power Query, llegaremos al mismo resultado.
Aprenderemos funciones M de Power Query muy potentes, como por ejemplo:
List.ReplaceMatchingItems(lista as list, reemplazos as list, optional equationCriteria as any) as list
donde se realizan los reemplazos especificados en la 'lista'.
La lista de 'reemplazos' consta de una lista de dos valores, el anterior y el nuevo.
Comencemos nuestro trabajo como siempre cargando en el editor de consultas la 'Tabla1' con los nombres completos... observa que aparecen todo tipo de nombres y apellidos (simples y compuestos).
Con la Tabla de nombres cargada, y ya en el editor de consultas creamos una Consulta en blanco (desde el menú Inicio > nueva Consulta > Nuevo Origen > Otros orígenes > Consulta en Blanco).
En el editor avanzado escribiremos el siguiente código (en algunos pasos podríamos emplear el asistente, mientras en otro se requiere conocimientos de lenguaje M específico):
Un código un poco largo, pero muy potente ;-)
Cerraremos y cargaremos sobre nuestra hoja de cálculo y listo.
Si comparamos con nuestra UDF comprobaremos que el resultado es el mismo... Asombroso!!.
Pero, ¿qué ha ocurrido en cada paso de nuestra consulta???.
En la primera sentencia o paso hemos creado una Lista:
ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}),
que muestra en distintas filas el valor a reemplazar seguido del valor nuevo. Lista necesaria para emplear la función M comentada List.ReplaceMatchingItems como segundo argumento...
El por qué de la barra vertical lo entenderás en los pasos siguientes...
El segundo paso es muy simple, sirve para cargar la 'Tabla1' previamente subida al editor de PQ.
NombresCompletos = Tabla1,
Siguiente paso. Transformamos la Tabla1 cargada, aplicandole la función Text.Proper
MayusculasCadaPalabra = Table.TransformColumns(NombresCompletos,{{"Nombre completo", Text.Proper, type text}}),
convirtiendo la inicial de cada palabra en mayúscula y el resto en minúsculas... se trata de homogeneizar el texto de la Tabla1 de nombres completos, para poder hacer coincidir con nuestra lista anterior de valores a reemplazar!.
En el paso siguiente añadimos una nueva columna 'Split1', donde cada registro se ha descompuesto como una lista de elementos; elementos que serán cada una de las palabras del nombre completo, pero seguido o concatenado de una barra vertical |
TurnTextToList = Table.AddColumn(MayusculasCadaPalabra, "Split1", each List.Transform(Text.Split([Nombre completo], " "), each _ & "|")),
Nuevamente un paso fundamental, ya que sobre esta lista aplicaremos la función List.ReplaceMatchingItems, siendo esta última lista generada el primer argumento.
Nuevo paso donde haremos uso por fin de List.ReplaceMatchingItems, y así efectuar el reemplazamiento necesario:
Replacements = Table.AddColumn(TurnTextToList, "Nombre_1", each Text.Combine(List.ReplaceMatchingItems([Split1],ListaReemp)," ")),
Añadimos una nueva columna 'Nombre_1' que combine el resultado de unir los distintos elementos de la lista descompuesta de cada nombre, que habíamos insertado en la columna 'Split1', con los reemplazamientos definidos en la 'ListaReemp'.
Aquí paramos para explicar qué está ocurriendo, y el por qué del uso de la barra vertical.
Habíamos descompuesto por palabras cada nombre completo, añadiendo al final la |... para luego reemplazar cada elemento coincidente con nuestra lista original:
{{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}
donde sustituimos las palabras clave, con su barra vertical |, por la misma palabra sin barra.
Esto supone, por tanto, que después del reemplazamiento únicamente quedan con | las palabras NO claves, las que no se emplean para componer los nombres o apellidos compuestos...
En el siguiente paso, de forma sencilla, eliminamos la última | incorporada, como último caracter:
MenosUltimoCaracter = Table.AddColumn(Replacements, "Nombre_2", each Text.Start([Nombre_1],Text.Length([Nombre_1])-1)),
En una nueva columna 'Nombre_2' obtenemos la cadena de texto sobre la que seguir trabajando.
Y vamos al siguiente paso. En una nueva columna separamos nuevamente, usando la barra vertical | como separador:
Ultimo_Split = Table.AddColumn(MenosUltimoCaracter, "UltimoSplit", each Text.Split([Nombre_2],"|")),
obteniendo una lista para cada registro de tantos elementos como palabras NO claves (ninguna de la de nuestra lista de reemplazos)
Si tenemos nombres completos debereíamos tener entre tres o cuatro elementos en esta lista...
Realizamos un conteo de elementos generados en cada lista en una nueva columna:
Cuenta_Partes = Table.AddColumn(Ultimo_Split, "CuentaElementos", each List.Count([UltimoSplit])),
Casi acabamos... paciencia ;-)
En los tres pasos siguientes obtendremos una columna para el 'Nombre', otra para el 'Apellido 1' y otro más para el 'Apellido 2'. Nos vasamos en el cálculo previo de número de elementos.
Si hay 4 elementos, parece claro que dos de ellos deberían ser el primer y segundo apellido, y los otros dos parte de un nombre compuesto...
Si solo hubiera 3 elementos, será un caso clásico de primer y segundo apellido junto con un nombre simple.
Esta idea es la que se plasma en los pasos:
AddNombre = Table.AddColumn(Cuenta_Partes, "Nombre", each if [CuentaElementos]>3 then
[UltimoSplit]{0}? & [UltimoSplit]{1}? else
[UltimoSplit]{0}?),
Add1erApellido = Table.AddColumn(AddNombre, "1er Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{2}? else [UltimoSplit]{1}?),
Add2doApellido = Table.AddColumn(Add1erApellido, "2do Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{3}? else [UltimoSplit]{2}?),
Nos debemos fijar en un par de aspectos relevantes.
El primero en la forma de trabajar con elementos concretos de una lista (recuerda que PQ trabaja en base 0), por ejemplo:
[UltimoSplit]{0} recupera el primer elemento
[UltimoSplit]{1} el segundo
[UltimoSplit]{2} el tercero
[UltimoSplit]{3} el cuarto
etc...
Por otra parte para evitar posibles 'errores' en caso de la NO existencia de dichos elementos aplicamos el operador ? al final de cada referencia
[UltimoSplit]{0}?
[UltimoSplit]{1}?
[UltimoSplit]{2}?
[UltimoSplit]{3}?
Muy práctico cuando el posible fallo está controlado...
Las distintas columnas añadidas recuperan unas posiciones u otras de nuestra lista previa generada en el campo 'UltimoSplit' en un paso previo.
En el último paso, por estética y limpieza, quitamos todos los campos sobrantes y finalizamos.
Un proceso algo largo (no tanto en realidad) donde se emplean muchas de las funciones mas recurrentes en Power Query, y algunas nuevas sobre las que me he detenido.
Por concluir el artículo de hoy, quería dejar abierta una alternativa, donde el listado de reemplazamientos quede a disposición del usuario en la hoja de cálculo.
Para ello necesitaríamos cargar esa tabla de 'reemplazamientos' al editor de consultas, y retocar las primeras líneas de la consulta anterior
Hace ya unos cuantos años publiqué un artículo done, con una UDF de VBA para Excel, se daba solución a casi todas las posibilidades de nombres y apellidos compuestos. Puedes releerlo aquí... un artículo muy interesante!!
Hoy empleando una técnica similar, pero con Power Query, llegaremos al mismo resultado.
Aprenderemos funciones M de Power Query muy potentes, como por ejemplo:
List.ReplaceMatchingItems(lista as list, reemplazos as list, optional equationCriteria as any) as list
donde se realizan los reemplazos especificados en la 'lista'.
La lista de 'reemplazos' consta de una lista de dos valores, el anterior y el nuevo.
Comencemos nuestro trabajo como siempre cargando en el editor de consultas la 'Tabla1' con los nombres completos... observa que aparecen todo tipo de nombres y apellidos (simples y compuestos).
Con la Tabla de nombres cargada, y ya en el editor de consultas creamos una Consulta en blanco (desde el menú Inicio > nueva Consulta > Nuevo Origen > Otros orígenes > Consulta en Blanco).
En el editor avanzado escribiremos el siguiente código (en algunos pasos podríamos emplear el asistente, mientras en otro se requiere conocimientos de lenguaje M específico):
let
/*Cargando datos de una tabla en la hoja de cálculo*/
//reemplazos = TablaReplacements,
//ListaReemp = Table.AddColumn(reemplazos, "Lista", each ({[Reemplaza], [por]})),
/*Cargando datos de la lista creada en M */
ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}),
/*Cargamos la tabla con los nombres completos a trabajar*/
NombresCompletos = Tabla1,
/*Aplicamos formato de 'Nombre propio' */
MayusculasCadaPalabra = Table.TransformColumns(NombresCompletos,{{"Nombre completo", Text.Proper, type text}}),
/*Separamos cada nombre en una lista, usando el espacio en blanco...
cada palabra le incorporamos un caracter | */
TurnTextToList = Table.AddColumn(MayusculasCadaPalabra, "Split1", each List.Transform(Text.Split([Nombre completo], " "), each _ & "|")),
/*Reemplazamos las coincidencias de ListaReemp
y unimos de nuevo los elementos separados en el paso anterior */
Replacements = Table.AddColumn(TurnTextToList, "Nombre_1", each Text.Combine(List.ReplaceMatchingItems([Split1],ListaReemp)," ")),
/*Eliminamos el último caracter | */
MenosUltimoCaracter = Table.AddColumn(Replacements, "Nombre_2", each Text.Start([Nombre_1],Text.Length([Nombre_1])-1)),
/*Separamos nuevamente por el separador | */
Ultimo_Split = Table.AddColumn(MenosUltimoCaracter, "UltimoSplit", each Text.Split([Nombre_2],"|")),
/*Contabilizamos el número de elementos en cada nombre */
Cuenta_Partes = Table.AddColumn(Ultimo_Split, "CuentaElementos", each List.Count([UltimoSplit])),
/*Y añadimos tres columnas Nombre, Apellido1 y Apellido2 en base al recuento anterior. */
AddNombre = Table.AddColumn(Cuenta_Partes, "Nombre", each if [CuentaElementos]>3 then
[UltimoSplit]{0}? & [UltimoSplit]{1}? else
[UltimoSplit]{0}?),
Add1erApellido = Table.AddColumn(AddNombre, "1er Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{2}? else [UltimoSplit]{1}?),
Add2doApellido = Table.AddColumn(Add1erApellido, "2do Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{3}? else [UltimoSplit]{2}?),
/*Eliminamos columnas que nos 'sobran' */
ColumnasQuitadas = Table.RemoveColumns(Add2doApellido,{"Nombre completo", "Split1", "Nombre_1", "Nombre_2", "UltimoSplit", "CuentaElementos"})
in
ColumnasQuitadas
Un código un poco largo, pero muy potente ;-)
Cerraremos y cargaremos sobre nuestra hoja de cálculo y listo.
Si comparamos con nuestra UDF comprobaremos que el resultado es el mismo... Asombroso!!.
Pero, ¿qué ha ocurrido en cada paso de nuestra consulta???.
En la primera sentencia o paso hemos creado una Lista:
ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}),
que muestra en distintas filas el valor a reemplazar seguido del valor nuevo. Lista necesaria para emplear la función M comentada List.ReplaceMatchingItems como segundo argumento...
El por qué de la barra vertical lo entenderás en los pasos siguientes...
El segundo paso es muy simple, sirve para cargar la 'Tabla1' previamente subida al editor de PQ.
NombresCompletos = Tabla1,
Siguiente paso. Transformamos la Tabla1 cargada, aplicandole la función Text.Proper
MayusculasCadaPalabra = Table.TransformColumns(NombresCompletos,{{"Nombre completo", Text.Proper, type text}}),
convirtiendo la inicial de cada palabra en mayúscula y el resto en minúsculas... se trata de homogeneizar el texto de la Tabla1 de nombres completos, para poder hacer coincidir con nuestra lista anterior de valores a reemplazar!.
En el paso siguiente añadimos una nueva columna 'Split1', donde cada registro se ha descompuesto como una lista de elementos; elementos que serán cada una de las palabras del nombre completo, pero seguido o concatenado de una barra vertical |
TurnTextToList = Table.AddColumn(MayusculasCadaPalabra, "Split1", each List.Transform(Text.Split([Nombre completo], " "), each _ & "|")),
Nuevamente un paso fundamental, ya que sobre esta lista aplicaremos la función List.ReplaceMatchingItems, siendo esta última lista generada el primer argumento.
Nuevo paso donde haremos uso por fin de List.ReplaceMatchingItems, y así efectuar el reemplazamiento necesario:
Replacements = Table.AddColumn(TurnTextToList, "Nombre_1", each Text.Combine(List.ReplaceMatchingItems([Split1],ListaReemp)," ")),
Añadimos una nueva columna 'Nombre_1' que combine el resultado de unir los distintos elementos de la lista descompuesta de cada nombre, que habíamos insertado en la columna 'Split1', con los reemplazamientos definidos en la 'ListaReemp'.
Aquí paramos para explicar qué está ocurriendo, y el por qué del uso de la barra vertical.
Habíamos descompuesto por palabras cada nombre completo, añadiendo al final la |... para luego reemplazar cada elemento coincidente con nuestra lista original:
{{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}
donde sustituimos las palabras clave, con su barra vertical |, por la misma palabra sin barra.
Esto supone, por tanto, que después del reemplazamiento únicamente quedan con | las palabras NO claves, las que no se emplean para componer los nombres o apellidos compuestos...
En el siguiente paso, de forma sencilla, eliminamos la última | incorporada, como último caracter:
MenosUltimoCaracter = Table.AddColumn(Replacements, "Nombre_2", each Text.Start([Nombre_1],Text.Length([Nombre_1])-1)),
En una nueva columna 'Nombre_2' obtenemos la cadena de texto sobre la que seguir trabajando.
Y vamos al siguiente paso. En una nueva columna separamos nuevamente, usando la barra vertical | como separador:
Ultimo_Split = Table.AddColumn(MenosUltimoCaracter, "UltimoSplit", each Text.Split([Nombre_2],"|")),
obteniendo una lista para cada registro de tantos elementos como palabras NO claves (ninguna de la de nuestra lista de reemplazos)
Si tenemos nombres completos debereíamos tener entre tres o cuatro elementos en esta lista...
Realizamos un conteo de elementos generados en cada lista en una nueva columna:
Cuenta_Partes = Table.AddColumn(Ultimo_Split, "CuentaElementos", each List.Count([UltimoSplit])),
Casi acabamos... paciencia ;-)
En los tres pasos siguientes obtendremos una columna para el 'Nombre', otra para el 'Apellido 1' y otro más para el 'Apellido 2'. Nos vasamos en el cálculo previo de número de elementos.
Si hay 4 elementos, parece claro que dos de ellos deberían ser el primer y segundo apellido, y los otros dos parte de un nombre compuesto...
Si solo hubiera 3 elementos, será un caso clásico de primer y segundo apellido junto con un nombre simple.
Esta idea es la que se plasma en los pasos:
AddNombre = Table.AddColumn(Cuenta_Partes, "Nombre", each if [CuentaElementos]>3 then
[UltimoSplit]{0}? & [UltimoSplit]{1}? else
[UltimoSplit]{0}?),
Add1erApellido = Table.AddColumn(AddNombre, "1er Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{2}? else [UltimoSplit]{1}?),
Add2doApellido = Table.AddColumn(Add1erApellido, "2do Apellido", each if [CuentaElementos]>3 then
[UltimoSplit]{3}? else [UltimoSplit]{2}?),
Nos debemos fijar en un par de aspectos relevantes.
El primero en la forma de trabajar con elementos concretos de una lista (recuerda que PQ trabaja en base 0), por ejemplo:
[UltimoSplit]{0} recupera el primer elemento
[UltimoSplit]{1} el segundo
[UltimoSplit]{2} el tercero
[UltimoSplit]{3} el cuarto
etc...
Por otra parte para evitar posibles 'errores' en caso de la NO existencia de dichos elementos aplicamos el operador ? al final de cada referencia
[UltimoSplit]{0}?
[UltimoSplit]{1}?
[UltimoSplit]{2}?
[UltimoSplit]{3}?
Muy práctico cuando el posible fallo está controlado...
Las distintas columnas añadidas recuperan unas posiciones u otras de nuestra lista previa generada en el campo 'UltimoSplit' en un paso previo.
En el último paso, por estética y limpieza, quitamos todos los campos sobrantes y finalizamos.
Un proceso algo largo (no tanto en realidad) donde se emplean muchas de las funciones mas recurrentes en Power Query, y algunas nuevas sobre las que me he detenido.
Por concluir el artículo de hoy, quería dejar abierta una alternativa, donde el listado de reemplazamientos quede a disposición del usuario en la hoja de cálculo.
Para ello necesitaríamos cargar esa tabla de 'reemplazamientos' al editor de consultas, y retocar las primeras líneas de la consulta anterior
let
/*Cargando datos de una tabla en la hoja de cálculo*/
reemplazos = TablaReplacements,
ListaReemp = Table.AddColumn(reemplazos, "Lista", each ({[Reemplaza], [por]})),
/*Cargando datos de la lista creada en M */
//ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}),
/*Cargamos la tabla con los nombres completos a trabajar*/
NombresCompletos = Tabla1,
// y el resto de la consulta sería igual...
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.