martes, 20 de febrero de 2018

Power Query: Columna dinámica

Seguimos con la herramienta Obtener y transformar/Power Query.
Hoy trabajaremos para conseguir, a partir de una tabla con campo 'Zona', 'Producto' e 'Importes', una nueva tabla resumen con importes acumulados de los 'productos' por cada 'zona' (a semejanza de una tabla dinámica'.

En particular nos aprovecharemos de una herramienta especialmente útil de Power Query: Columna dinámica / Unpivot.

Power Query: Columna dinámica



Como siempre, desde la tabla, cargamos los datos en el editor de consultas:
Desde la hoja de cálculo accedemos a la Ficha Datos > grupo Obtener y transformar > Desde una tabla.


Ya en editor de consulta, y con nuestra consulta habilitada, seleccionamos el campo 'Zona', y navegamos hasta el menú Transformar > grupo Cualquier columna > botón Columna dinámica

Power Query: Columna dinámica



Se abrirá una ventana de configuración donde ajustar la conversión.
Seleccionaremos como 'columna de valores' el campo numérico ('Importes'); y como función de valor agregado (que se empleará para agrupar los datos cruzados) la Suma.

Power Query: Columna dinámica



Hemos acabado. Podemos Cerrar y Cargar para obtener el resultado mostrado al inicio de la explicación.
Tenemos un tabla de referencia cruzada por zona y producto, y resumido por suma de importes acumulados...

Y como una tabla dinámica, nuevos elementos se incorporarán automáticamente en nuestro informe resumen.


A modo de curiosidad, si entramos en el editor avanzado de la consulta veremos:

let 
    Origen = Excel.CurrentWorkbook(){[Name="Tbl_Pdtos"]}[Content],
    #"Columna dinamizada" = Table.Pivot(Origen, List.Distinct(Origen[Zona]), "Zona", "Importes", List.Sum)
in
    #"Columna dinamizada"


Donde observamos el uso de la función Table.Pivot, que es precisamente la que habilita la posibilidad de crear nuevas columnas por cada elemento del campo indicado.

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!.