jueves, 15 de febrero de 2018

Power Query: Listado elementos únicos

Aprenderemos algo más sobre el uso de esta gran herramienta: Obtener y transformar (Power Query)

Hoy en concreto veremos como conseguir un listado de elementos únicos, sin repetición, empleando la función Table.Distinct,
que podemos obtner desde las opciones del Editor de consultas, con un viejo conocido de la hoja de cálculo, como es el Quitar duplicados.

Power Query: Listado elementos únicos



A partir de la tabla azul, de la izquierda (de nombre 'Tbl_Pdtos') llamaremos al Editor de consultas desde la ficha Datos > grupo Obtener y transformar > botón Desde una tabla

Con la tabla cargada, le cambiamos el nombre la consulta y la llamo 'Quitar_Duplicados'.
En el paso siguiente seleccionamos el campo 'Producto', y hacemos clic derecho para seleccionar la opción: Quitar otras columnas. Esto elimina el resto de columnas de la tabla cargada, dejando visible únicamente la del campo 'Producto'

Power Query: Listado elementos únicos



YA con solo un campo visible, realizamos la última acción.
Desde la ficha de Inicio de editor de consultas > grupo Reducir filas > desplegable Quitar filas > Quitar duplicados

Power Query: Listado elementos únicos



Listo. Podemos Cerrar y Cargar el resultado en nuestra hoja de cálculo... obteniendo una tabla de elemento únicos como veíamos en la primera imagen.

Si accedemos al Editor avanzado de la consulta veríamos el siguiente código:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tbl_Pdtos"]}[Content],
    #"Otras columnas quitadas" = Table.SelectColumns(Origen,{"Producto"}),
    #"Duplicados quitados" = Table.Distinct(#"Otras columnas quitadas")
in
    #"Duplicados quitados"


Comprobamos como la acción de Quitar duplicados ha dejado grabada la función Table.Distinct

Otra forma de llegar a lo mismo sería directamente insertar una consulta en blanco (ficha Inicio > grupo Nueva consulta > Nuevo origen > Otros orígenes > Consulta en blanco).
Y desde el editor avanzado escribir:

Table.Distinct(  
    Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Tbl_Pdtos"]}[Content],{"Producto"})
, "Producto")


Sin duda más simple y directo.. pero con igual resultado que con el asistente.

martes, 13 de febrero de 2018

BUSCARV sobre varios origenes

Muchas veces a lo largo de mis años como consultor y formador me han plantado la misma cuestión. (¿es posible hacer un BUSCARV sobre diferentes rangos?)... y en la entrada anterior analizamos una manera de recuperar un valor buscado que podría encontrarse en diferentes tablas... con un par de inconvenientes:
1-se necesitaba una acción manual de refresco de la consulta de Power Query,
2-estábamos restringidos por el número de tablas.

Hoy veremos un trabajo similar sin ninguna de las limitaciones anteriores... (solo estaremos sujetos a la que nos marca el uso de fórmulas matriciales).
Así pues empecemos a construir un solo BUSCARV sobre infinitos orígenes.


Partimos en este caso de tres tablas (pero podrían ser las que necesitáramos):

BUSCARV sobre varios origenes



Necesitamos tener una lista con los nombres de las diferentes tablas, en mi caso los he dispuesto en el rango B13:B15:
Tbl_135
Tbl_246
Tbl_78910

A cuyo rango además he asignado el nombre definido: Lista.

Hasta este punto tenemos pues dos nombres definidos:
lista =BUSCARV_Multiple!$B$13:$B$15
vBuscado =BUSCARV_Multiple!$B$8


Estamos preparados... en la celda C8 introducimos la fórmula matricial buscada:
=BUSCARV(vBuscado;INDIRECTO(INDICE(lista;SUMA((CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0)*FILA(INDIRECTO("1:"&CONTARA(lista))));1));3;0)

(recuerda presionar Ctrl+Mayusc+Enter para validar en lugar de solo Enter).
El resultado aparece mágicamente.. dará igual en qué tabla se encuentre, si existe en alguna de ellas, obtendremos el valor deseado!.


la explicación.
La clave del asunto es el uso de la función:
CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0
que devuelve una matriz de VERDADEROS y FALSO, una por cada tabla... y tendremos un VERDADERO solo para la tabla donde se encuentre el valor buscado, por ejemplo, para el valor buscado 'p7' tendríamos la matriz:
{FALSO;FALSO;VERDADERO}
es decir, es cierto que 'p7' la encontramos en la tercera tabla...


Para convertir esa matriz {FALSO;FALSO;VERDADERO} en un tres (tercera tabla) lo multiplicamos por una matriz de constantes {1;2;3}
lo que conseguimos con la clásica fórmula
FILA(INDIRECTO("1:"&CONTARA(lista)))
que nos devolverá la matriz de naturales desde 1 hasta el número de tablas existente.

El producto elemento a elemento y su suma final nos retornará el número buscado: 3 (para este ejemplo).


Si aplicamos sobre nuestra 'lista' de Tablas la función INDICE, nuestro tres devuelve el nombre de la Tabla deseada... si sobre ella aplicamos la función INDIRECTO ya podremos trabajar sobre esa tabla igual que si la seleccionáramos.

Si ya tenemos la tabla, con la fórmula comentada:
INDIRECTO(INDICE(lista;SUMA((CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0)*FILA(INDIRECTO("1:"&CONTARA(lista))));1))

estamos en disposición de aplicar un BUSCARV como haríamos normalmente:
=BUSCARV(vBuscado; tabla_recuperada_con_nuestra_fórmula;3;0)


Objetivo logrado!.

jueves, 8 de febrero de 2018

Power Query: Búsqueda sobre varias tablas

Hoy aprenderemos a generar una búsqueda vertical sobre distintos orígenes/tablas empleando la herramienta Obtener y Transformar (antiguo Power Query).

En definitiva daremos respuesta a una cuestión clásica: ¿podemos realizar una especie de 'BUSCARV' sobre diferentes tablas?.


En primer lugar partiremos, para no eternizar el ejemplo, de tres tablas (a las que he llamado 'Tbl_135', 'Tbl_246' y 'Tbl_78910'):



Por otro lado hemos asignado un Nombre definido a una celda que contendrá el valor a buscar:
vBuscado =Hoja1!$B$8


En el siguiente paso, accederemos una vez por cada tabla, a la ficha Datos > grupo Obtener y Transformar > Desde una tabla.
Nos aseguraremos que solo cargaremos la Conexión !!


En el siguiente paso crearemos una consulta anexando las tres tablas conectadas.
Dentro del Editor de consultas en la ficha Inicio > desplegable Combinar > Anexar consultas > Anexar consultas para crear una nueva

Power Query: Búsqueda sobre varias tablas


A esta nueva consulta le he renombrado como 'TablaTotal'.

Hace tiempo publiqué cómo crear funciones personalizadas dentro del entorno de Power Query (ver).
Bien, tendremos que recordar el proceso.

Creamos una consulta en blanco:
Ficha Datos > grupo Obtener y Transformar > desplegable Nueva Consulta > Desde Otras fuentes > Consulta en blanco
Accederemos al Editor Avanzado (ir a ficha Vista dentro del Editor de consultas) e introduciremos el siguiente código:

(NombreDefinido) => 
    Excel.CurrentWorkbook(){[Name=NombreDefinido]}[Content]{0}[Column1]

Aprovechamos para cambiar el nombre de la consulta/función por 'ValorBuscado'


Por último volvemos a la configuración de nuestra TablaTotal que ahora mismo solo contiene la línea para combinar las tres tablas originales.
Editamos de forma avanzada esta consulta y la dejamos como sigue:

let
    Origen = Table.Combine({Tbl_135, Tbl_246, Tbl_78910}),
    #"Filas filtradas" = Table.SelectRows(Origen, each ([Id] = ValorBuscado("vBuscado"))),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Id", "concepto"})
in
    #"Columnas quitadas"


Lo que hemos añadido es una acción de filtrado sobre el campo [Id] de nuestra tabla única, donde filtramos por el valor que hubiera en nuestra celda de la hoja de cálculo con el nombre definido asignado 'vBuscado' (revisa primeros pasos).
#"Filas filtradas" = Table.SelectRows(Origen, each ([Id] = ValorBuscado("vBuscado"))),

Y en la última línea simplemente nos quedamos con el campo que nos interesa... el precio. Para ello eliminamos las dos columnas "Id", "concepto"
#"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Id", "concepto"})


Listo, podemos Cargar y cerrar nuestra consulta y devolver el resultado a la hoja de cálculo.

Power Query: Búsqueda sobre varias tablas


Para comprobar su correcto funcionamiento bastará que cambies el valor en la celda amarilla (B8 que tiene asignada el nombre definido - vBuscado-) y actualizar la consulta de Power Query.