martes, 13 de abril de 2021

Power Query: Recuperar datos en cualquier posición

Veremos en el día de hoy un ejercicio desarrollado con Power Query que nos permite recuperar información de una hoja (elegida a nuestra discreción) de un libro de trabajo; con la particularidad que cada hoja tiene los datos distribuidos por filas y columnas un tanto desordenados... Veamos la imagen:
Power Query: Recuperar datos en cualquier posición

Power Query: Recuperar datos en cualquier posición

Power Query: Recuperar datos en cualquier posición

Podemos observar que el único punto en común de las tres hojas es la existencia de cuatro campos iguales: Fecha, Cuenta, Debe y Haber; colocados en filas y columnas distintas...
Esto descartaría a priori la creación de una consulta con patrón único para las tres.

Pero veamos una posible solución al problema, empleando Table.FindText, List.PositionOf y Table.PositionOf.
Partiremos de una hoja donde tengamos una celda con validación de datos tipo lista que permita elegir entre una de las tres hojas de nuestro libro origen (celda B1 y nombre asignado 'ndPaís'), y una Tabla ('tblCampos') con los campos que nos interese recuperar de las distintas hojas (en el orden establecido).
Power Query: Recuperar datos en cualquier posición

Obviamenete lo primero que haremos será cargar al Editor de Power Query la tabla y la celda comentadas solo como conexión.
Lal tabla sin modificación alguna, y la celda 'ndPaís' que convertiremos en valor 'desagrupando datos'... o personalizando desde el editor avanzado hasta que quede:
let
    Origen = Excel.CurrentWorkbook(){[Name="ndPais"]}[Content]{0}[Column1]
in
    Origen
Power Query: Recuperar datos en cualquier posición


Y ahora desde el Editor de Power Query añadiremos una Consulta en blanco con el siguiente código:
let
    // vinculamos con el fichero fuente (con sus tres hojas:ES, FR e DE)
    Origen = Excel.Workbook(File.Contents("F:\excelforo\PQ_PositionofAny_origen.xlsx"), null, true),
    // filtramos por el valor de la celda 'ndPaís', según el nombre de la hoja
    FilasFiltrada_PAIS = Table.SelectRows(Origen, each ([Name] = pPais)),
    // nos quedamos solo con el campo 'Data' que expandimos a continuación
    OtrasColumnasQuitadas = Table.SelectColumns(FilasFiltrada_PAIS,{"Data"}),
    Expande_Data=Table.Combine(OtrasColumnasQuitadas[Data]),

    // Eliminamos columnas vacías o sin ningún dato
    LimpiaColumnas=
        Table.SelectColumns(Expande_Data, List.Select(Table.ColumnNames(Expande_Data), each List.NonNullCount(Table.ToColumns(Table.SelectColumns(Expande_Data, _)){0})>0)),
    // obtenemos la lista ordenada de los campos de la tblCampos
    CamposOrd=tblCampos[Lista_Campos],
    // identificamos la fila donde coinciden/intersectan los campos buscados con los existentes
    // empleamos List.Intersect
    RfL=List.Transform(Table.ToRows(LimpiaColumnas), each List.Intersect({CamposOrd,List.Select(_, each _ <> null)})),
    // List.PositionOf devuelve la posición
    FilaFecha=List.PositionOf(RfL,CamposOrd),

    // con Table.FindText encontramos y recuperamos la fila completa donde localice el texto buscado (Usuario:)
    FilaUsuario=Table.PositionOf(LimpiaColumnas,Table.FindText(LimpiaColumnas,"Usuario:"){0}),
    // Table.Range retorna las filas de una tabla discriminando filas fuera del rango de filas
    // para eliminar filas superiores es habitual emplear Table.Skip
    TablaFinal=Table.Range(LimpiaColumnas, FilaFecha,FilaUsuario-FilaFecha),

    // subimos encabezados y eliminamos filas sin datos
    EncabezadosPromovidos = Table.PromoteHeaders(TablaFinal, [PromoteAllScalars=true]),
    FilasBlancoEliminadas = Table.SelectRows(EncabezadosPromovidos, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

    // ... por si aacaso volvemos a lanzar la instrucción de eliminar columnas sin datos
    RelimpiaColumnas= Table.SelectColumns(FilasBlancoEliminadas, List.Select(Table.ColumnNames(FilasBlancoEliminadas), each List.NonNullCount(Table.ToColumns(Table.SelectColumns(FilasBlancoEliminadas, _)){0})>0)),

    // acabamos asignando tipo de datos a Fecha y seleccionamos las columnas elegidas en la tblCampos
    TipoCambiado = Table.TransformColumnTypes(RelimpiaColumnas,{{"Fecha", type date}}),
    SeleccionColumnas = Table.SelectColumns(TipoCambiado,CamposOrd)
in
    SeleccionColumnas

Power Query: Recuperar datos en cualquier posición

Aceptamos y cargamos sobre la hoja de cálculo... solo tendremos que elegir un país/hoja en la celda B1 y actualizar la consulta para mostrar los datos contenidos en dicha hoja.

De especial interes es la función M:
List.Intersect(lists as list, optional equationCriteria as any) as list
que devolvería una lista con los elementos que crucen.
Otra función relevante es: Table.ToRows(table as table) as list
que genera una lista de listas con cada fila de la tabla dada.
Sobre esta lista de listas-filas aplicamos List.Select:
List.Select(list as list, selection as function) as list
con el que recuperamos listas de elementos que cumplan el criterio.
En nuestro ejemplo:
List.Select(_, each _ <> null)
que aplica sobre cada lista de Table.ToRows.
Acabamos llamando a: List.PositionOf(list as list, value as any, optional occurrence as nullable number, optional equationCriteria as any) as any
que nos dice en qué posición de la lista dada se encuentra lo que buscamos...

En definitiva obtendremos listas por cada fila, solo cuando tenga dato, dejando fuera los elementos nulos.
Power Query: Recuperar datos en cualquier posición

Este paso es clave para identificar el inicio de nuestro encabezado...


Otro paso importante es el empleo de:
Table.FindText(table as table, text as text) as table
que devuelve una tabla con aquellas filas donde se encuentre el texto buscado...
En nuestro ejemplo, nos sirve para localizar la fila donde se encuentre el fin de los datos.. ya que sabemos que el texto 'Usuario:' indica el fin del informe.
Nos apoyamos además en Table.PositionOf(table as table, row as record, optional occurrence as any, optional equationCriteria as any) as any para conocer el número de fila:
FilaUsuario=Table.PositionOf(LimpiaColumnas,Table.FindText(LimpiaColumnas,"Usuario:"){0})
Fíjate que al añadir {0} al resultado de la tabla obtenemos un Registro, que es lo que pide la función Table.PositionOf

El resto de código esta explicado en los comentarios del editor avanzado y no supone dificultad alguna.

Empleando estas funciones de 'busqueda' (List.PositionOf, Table.PositionOf, Table.FindText y alguna otra comentada en este post) conseguimos localizar encabezados y fin de datos, independientemente de dónde se hallen... un éxito!

jueves, 8 de abril de 2021

Vectores de Subtotales sobre rango dinámico

En un artículo de hace un par de semanas tuve la necesidad de verificar, para datos de una tabla y por cada registro, si se verificaban una serie de criterios (múltiples) leer aquí.
Descubrir si se cumplian esos ciertoso criterios fue relativamente simple empleando ciertos condicionales SI, con lo que se obtenía una matriz de 1 y 0.
Posteriormente tuve la necesidad de, a partir de dicha matriz de 1 y 0, simplificarla en un Vector de VERDADERO y FALSO, que sencillamente reconociera si existe en cada fila al menos un cumplimiento... o dicho de otro modo, si la suma de cada fila es mayor a cero.
Empezaremos con una matriz aleatoria dinámica de 0 y 1 (de dimensión 12 filas x 3 columnas).
=MATRIZALEAT(12;3;0;1;VERDADERO)

Cada columna simula el cumplimiento (=1) o no (=0) de los tres supuesto criterios existentes...
Vectores de Subtotales sobre rango dinámico

Nuestro primer paso es obtener el vector vertical para cada fila que sume las tres columnas de cada registro...
Entonces en F2 escribimos:
=MMULT(B2#;SECUENCIA(COLUMNAS(B2#);1;1;0))
donde multiplicamos dos matrices, una de (12 x 3) y otra de (3 x 1), lo que obviamente devolverá como resultado un vector de dimensión (12 filas x 1 columna).
Recuerda la regla matemática del producto de matrices:
A(m x n) x B(n x z) = C(m x z)
En nuestro caso nos hemos apoyado en la función SECUENCIA para montar una matriz de unos con dimensión (3x1) = {1;1;1} como necesitábamos...

Lo que vemos es un vector vertical que suma fila por fila los tres elementos de la matriz principal.
Si no tuvieras disponible la función SECUENCIA podrías replicarla matricialmente de la siguiente manera:
=MMULT(B2#;FILA(INDIRECTO("1:"&COLUMNAS(B2#)))^0)

En cualquiera de las dos formas, en un paso posterior, habría que convertirlo en prueba lógica, comparando con '>0' para obtener VERDADEROS y FALSOS, y así poder operar con el vector más facilmente.
=MMULT(B2#;SECUENCIA(COLUMNAS(B2#);1;1;0))>0
Vectores de Subtotales sobre rango dinámico


Adicionalmente, sin utilidad para el caso planteado (pero sí para otros), mostraré como lograr el vector horizontal de suma por columnas.
En este caso necesitaremos generar un vector de unos, de dimensión 1 fila x 12 columnas, de tal forma que el producto de matrices quede: (1x12) x (12x3) = (1x3).

Para ello podemos escribir en B15:
=MMULT(SECUENCIA(1;FILAS(B2#);1;0);B2#)
o, matricialmente, para otras versiones de Excel:
=MMULT(TRANSPONER(FILA(INDIRECTO("1:"&FILAS(B2#))))^0;B2#)
Vectores de Subtotales sobre rango dinámico


Fórmulas muy potentes por si mismas para obtener esos parciales por filas y/o columnas, pero también como vectores auxiliares de apoyo para construir fórmulas más elaboradas...

martes, 6 de abril de 2021

Power Pivot: Medidas y Funciones CUBO

En entradas previas del blog estuvimos practicando con funciones estándar como SUMAPRODUCTO (ver aquí) y las especiales funciones CUBO y el lenguaje MDX (ver aquí) para conseguir replicar el comportamiento de las funciones BD, es decir, poder operar sobre una base de datos a partir de un rango de criterios.

Hoy veremos una nueva opción trabajando con DAX dentro de Power Pivot para Excel.

En lugar de generar expresiones en MDX dentro de la función CONJUNTOCUBO para crear el conjunto de elementos requerido que cumpla las condiciones, crearemos dentro del Modelo de datos una Medida para recuperarla directamente con la función VALORCUBO.
Seguro que te sorprenderá :OO
Power Pivot: Medidas y Funciones CUBO

Ya teníamos cargada nuestra Tabla de Ventas ('Tabla1') en el Modelo de datos. A continuación repetiremos la acción y cargaremos la tabla de criterios ('TblCRITERIOS').
Y NO LA RELACIONAREMOS!!, es decir, vamos a trabajar con tablas NO relacionadas.
Este es un punto importante, y que aporta algo de complejidad a nuestra labor.

Este sería nuestro diagrama de relaciones en el Modelo:
Power Pivot: Medidas y Funciones CUBO

Aunque en teoría sería posible (y casi siempre recomendado) crear directamente la Medida en el Modelo, he de reconocer, que tras varios intentos me rendí y no pude lograrlo (ojalá algún experto en DAX me indique la forma), por lo que me apoyé en una columna calculada para lograrlo.

Así pues, dentro de la 'Tabla1' de ventas en el Modelo añadimos la siguiente columna calculada (que llamaré CRITERIOS) con la fórmula:
=VAR XX=
FILTER(TblCRITERIOS;
IF(TblCRITERIOS[Comercial]="";"1";TblCRITERIOS[Comercial]=(Tabla1[Comercial]))&&
IF(TblCRITERIOS[País]="";"1";TblCRITERIOS[País]=(Tabla1[País]))&&
IF(TblCRITERIOS[Producto]="";"1";TblCRITERIOS[Producto]=(Tabla1[Producto])))
RETURN
CALCULATE(COUNTROWS(TblCRITERIOS);XX)>0

Donde se cuenta el número de veces que los datos de Tabla1 cumplen los criterios de la TblCRITERIOS... devuelve TRUE si lo verifica al menos una vez.
Power Pivot: Medidas y Funciones CUBO

Obtendremos una columna de TRUE y FALSE, línea por línea, sabiendo si éstas cumplen alguno de los criterios de la tabla de criterios TblCRITERIOS.
Sobre la columna calculada ya operativa, generaremos una simple Medida:
SumCRIT:=SUMX(Tabla1;[Ventas]*[CRITERIOS])
Power Pivot: Medidas y Funciones CUBO

Con la medida 'SumCRIT' creada ya podemos volver a la hoja de cálculo y recuperar su información bien con tablas dinámicas (lo que haré para verificar el resultado devuelto) o bien con las funciones CUBO.
Power Pivot: Medidas y Funciones CUBO
Así en la celda S10 insertaremos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[SumCRIT]]";N10)
donde hacemos uso de nuestra recien generada medida, y nos apoyamos en el conjunto de años creado en la celda N8 con la función:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2019]";
"Años-13_19")
y desplegados sus elementos en N10 hasta N16 con:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";N8;SECUENCIA(RECUENTOCONJUNTOCUBO(N8)))
visto y explicado en post previos...

Llegado el momento de las comprobaciones observamos como en todos los casos (tabla dinámica, SUMAPRODUCTO, MDX o Medidas) el resultado es coincidente... trabajando correctamente, evitando duplicidades, en caso de registros que cumplan varias condiciones simultaneamente.
Por ejemplo, si te fijas en la 'Tabla1' en la fila 14, puedes ver como cumple DOS de las condiciones:
-corresponde a ANA y ES
-tiene un P1
pero obviamente NO duplica el importe en los cálculos...

Una forma distinta de trabajar con datos cargados en Power Pivot, que exige un conocimiento alto del lenguaje DAX para construir nuestra medida... pero que, a cambio, ofrece seguridad en el cálculo.

OJO, por que trabajar empleando medidas, en lugar de expresiones MDX, nos exige una Actualización de datos!!