martes, 27 de agosto de 2019

Power Query: Agregar Columna a partir de un ejemplo

Tiempo atrás, en un par de artículos del blog, expuse algunas maneras de recuperar información de una cadena de texto, la cual no seguía un patrón claro a la hora de la 'extracción' de caracteres.
Ver post1 y post2

Te recomiendo les eches un vistazo... ;-)


En el día de hoy emplearemos una herramienta muy potente para conseguir similares resultados, sin duda muy potente y versátil: Agregar columna a partir de los ejemplos

Esta herramienta trabaja de forma casi idéntica al Relleno rápido en la hoja de cálculo.

Iniciaremos a partir de un listado de alfanuméricos, de los cuales necesitamos separar la primera parte de dichas cadenas que sean caracteres de texto:

Power Query: Agregar Columna a partir de un ejemplo



Como siempre, en un primer paso Cargaremos la Tabla de datos en el Editor de consultas de Power Query.
Una vez cargados los datos, y dentro del Editor de consultas, accederemos a la Ficha Agregar columna > grupo General > botón Columna a partir de los ejemplo

Power Query: Agregar Columna a partir de un ejemplo



Seleccionaremos la columna a tratar y elegiremos la opción del botón desplegable: A partir de la selección.
esto abrirá el Asistente, con una columna 'personalizada' donde iremos completando cuantos elementos sean necesarios hasta visualizar los elementos como necesitáramos.

Por ejemplo, para el primer elemento (A1234PQ) escribimos una A, y el asistente propone como regla o patrón: caracteres por la izquierda hasta el '1'.

Power Query: Agregar Columna a partir de un ejemplo


Como el resultado no es el esperado completaremos el segundo elemento (MX51234R) con MX, proponiendo como regla para mis datos: eliminar caracteres del 1 al 5 y de la P a la R

Power Query: Agregar Columna a partir de un ejemplo


Seguimos lejos del resultado buscado.
Así que informamos para el tercer elemento(MS612345AB) con un MS, lo que marca una regla un poco compleja a base de separadores... que en cualquier caso no responde a nuestro objetivo.

Power Query: Agregar Columna a partir de un ejemplo


Seguimos con el proceso...
ciertamente podrías pensar que para hacer elemento a elemento no necesitamos Power Query, pero piensa en un listado de 50.000 registros con elementos sin un patrón claro.


Repetimos el proceso una vez más para el quinto elemento (OPA7123456VW) donde indicamos como respuesta OPA... y las propuestas restantes sí parecen responder a lo que queremos.

Power Query: Agregar Columna a partir de un ejemplo



Aceptamos la fórmula propuesta. Podemos verla desde el Editor avanzado:
let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"datos", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Tipo cambiado", "Personalizado", 
            each let splitdatos = Splitter.SplitTextByCharacterTransition((c) => 
            not List.Contains({"0".."9"}, c), {"0".."9"})([datos]) in splitdatos{0}?, type text)
in
    #"Added Custom Column"

Cargamos y cerramos la consulta y la llevamos a la hoja de cálculo, viendo el resultado.

Además, al añadir nuevos elementos comprobamos cómo el funcionamiento de ésta consulta es correcto.

Power Query: Agregar Columna a partir de un ejemplo



Al fin y al cabo el asistente, con nuestras pistas ha sido de encontrar la regla, patrón o pauta deseada para devolvernos los caracteres hasta el primer caracter numérico.

De forma similar podríamos obtener la parte final, o la parte numérica intermedia.

miércoles, 21 de agosto de 2019

Power Query: Combinar consultas con Coincidencias aproximadas

En una reciente actualización de Microsoft Excel se ha implementado dentro de Power Query una interesante funcionalidad a la hora de Combinar Consultas, donde se permiten combinar elementos entre consultas basándose en coincidencias parciales o aproximadas.

Veamos su uso.
Partimos de una tabla inicial ('TblUNO') con todos los datos correctos y como deberían aparecer en 'todos los sitios'... con dos campos: Nombre y Valor.
En una segunda tabla ('TblRESULTADO') donde nos aparecen los nombres (que deberían coincidir) escritos de maneras muy dispares...
sin tildes, abreviados, orden cambiado, etc...



En primer lugar cargaremos como conexión solo las dos tablas comentadas.
En el siguiente paso, desde el Editor de consultas de Power Query procederemos a Combinar ambas consultas.
Desde la ficha Inicio > botón desplegable Combinar > Combinar consultas > Combinar consultas para crear una nueva

Power Query: Combinar consultas con Coincidencias aproximadas



En la ventana que se abrirá elegiremos nuestras dos tablas a combinar y seleccionaremos los campos clave de 'nombres'

Power Query: Combinar consultas con Coincidencias aproximadas


Si no marcásemos opción alguna, y dejáramos tal cual la combinación, el resultado sería equivalente a un BUSCARV, i.e., solo localizaría las coincidencias exactas.

Power Query: Combinar consultas con Coincidencias aproximadas


Nada novedoso.

Lo interesante viene ahora, si optamos por marcar la opción Usar las coincidencias aproximadas para comparar la combinación, donde automáticamente ofrece una combinación con un Umbral de similitud al 0,8 (umbral entre 0 y 1).
Este umbral de similitud es editable, e implica que valores próximos a uno-1 permitirá coincidencias exactas, y valores cercanos a cero-0 hará que todos los elementos coincidan unos con otros.

Otras opciones que se nos ofrecen son:
Ignorar mayúsculas y minúsculas
Hacer coincidir mediante la combinación de las partes del texto


Power Query: Combinar consultas con Coincidencias aproximadas


Dejaré para un futuro post las otras opciones..

Si realizamos algunas pruebas cambiando el Umbral de similitud comprobaremos de primera mano el comportamiento de esas coincidencias.

Power Query: Combinar consultas con Coincidencias aproximadas


Se observa que para elementos similares 'aparentemente' como 'Isabel Romero' e 'Ismael Romero' las coincidencias aproximadas devuelven coincidencia!!, lo que nos generará un problema...
Igualmente problemático son algunos elementos abreviados: 'J. Perez' y 'Juan Pérez' donde para forzar la coincidencia debemos indicar un Umbral de 0.

Por supuesto, lo interesante de esta funcionalidad es que poco a poco nos permitirá ir depurando nuestras bases de datos, con el peligro latente de continuar nuestros cálculos con coincidencias erróneas
:'(

miércoles, 14 de agosto de 2019

Power Query: Anexar todas las tablas de un libro

Hoy toca una función M de Power Query que nos permite acceder a datos: Excel.CurrentWorkbook()
En concreto conseguiremos, de una sola vez, anexar todas las tablas existentes en nuestro libro actual de trabajo... haya las que haya en cada momento!!.
Pensando en libros con información creciente...

En nuestro ejemplo partiremos de un libro con dos tablas, llamadas:
TblESP
TblGER

Para este ejercicio, y en general en cualquier uso estándar de Excel, el uso de patrones a la hora de asignar nombres a tablas, o nombres definidos, nos ayudará en siguientes etapas.
En mi caso siempre nombre a las tablas siguiendo el patrón 'TblXXXX'

Nuestras tablas en imágenes:

Power Query: Anexar todas las tablas de un libro


Recuerda que el número de tablas con el que trabajar es indeterminado... pero necesitamos anexar cuantas existan!

Accedemos al Editor de 'consultas en blanco' de Power Query (Obtener y transformar).
Ficha Datos > Obtener y transformar > Obtener datos > Desde otras fuentas > Consultas en blanco

Power Query: Anexar todas las tablas de un libro



Ya en el Editor de consultas, nombraremos a nuestra consulta como 'TodasLasTablas' y desde la barra de fórmula (o desde el editor avanzado) escribiremos:
=Excel.CurrentWorkbook()

Power Query: Anexar todas las tablas de un libro


Al presionar 'Enter' tras escribir nuestra función M se generará una Tabla con todas las tablas y nombres definidos que exista en nuestro libro actual...
Aquí es donde cobra fuerza el haber tenido la precaución de llamar a nuestras tablas con el patrón comentado 'TblXXXX', ya que ahora vamos a aplicar un filtro al campo 'Name' de tipo texto, que comience por 'Tbl'.
Esto nos mostrará exclusivamente nuestras tablas de trabajo.

Power Query: Anexar todas las tablas de un libro


En este momento no notaremos diferencia alguna, pero aplicar este filtro es fundamental para evitar recurrencias y registros duplicados en pasos siguientes!!.

Penúltimo paso, expandimos el campo 'Content' de la consulta de PQ que estamos creando.

Power Query: Anexar todas las tablas de un libro


Al gusto de cada uno marcaremos/desmarcaremos la opción de Usar el nombre de la columna original como prefijo.
Al aceptar esto es lo que veremos:

Power Query: Anexar todas las tablas de un libro



Ya podemos 'Cerrar y cargar en' con destino una celda de nuestro libro de trabajo:

Power Query: Anexar todas las tablas de un libro



Lo interesante es que si incluimos una nueva tabla de otro país 'TblFRA', bastará actualizar nuestra consulta para ver los datos refrescados... ¡Increible!

Power Query: Anexar todas las tablas de un libro


Por supuesto las tablas pueden estar ubicadas en hojas diferentes de nuestro libro.