Power Pivot: NATURALINNERJOIN no detecta campos comunes

Un problema frecuente con el que nos encontramos al intentar cruzar diferentes tablas, es que al utilizar funciones del tipo NATURALINNERJOIN, nos devuelve mensajes de error del tipo: 'No encontrados campos comunes'.

Veamos, a partir del caso del artículo anterior un ejemplo y su solución.

Tenemos un supuesto donde combinaremos datos de una tabla 'real' con otra 'estática', donde cruzaremos ambas con la función NATURALINNERJOIN, la cual nos dará un problema incomprensible:
Indicará que no encuentre campos en común... a pesar de que estemos viendo los campos en las dos tablaas con exactamente el mismo nombre.

Aquí el problema no es, en realidad, el nombre del campo... si no que DAX no identifica el mismo tipo de datos en ambas columnas (aunque sí lo tenga :( ).

El truco-solución será, algo trabajado, emplear SELECTCOLUMNS para recuperar las columnas necesarias, lo que nos permitirá homogeneizar tanto el nombre de las columnas (por si hubiera alguna pega), como el tipo de datos.
¿cómo?, añadiendo un +0 si el tipo es numérico (INTEGER, DECIMAL, CURRENCY o DOUBLE), y añadiendo un &"" si fuera STRING.
Fíjate en el código siguiente:

TablaEstatica:=VAR tblTIPO=SELECTCOLUMNS(DATATABLE("Vendedor";STRING;"Tarifa";DOUBLE;
						 {{"JUAN";100};{"EVA";150};{"BEA";200}} );
						 "Vendedor";[Vendedor]&"";
						 "Tarifa"; [Tarifa]+0)
VAR TblJUAN_P2=SELECTCOLUMNS(FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[artículo]="p2");
						"Vendedor";[Vendedor]&"";
						 "Uds"; [Unidades vendidas]+0)
VAR CRUCE=NATURALINNERJOIN(TblJUAN_P2;tblTIPO)
VAR calculo=SUMX(CRUCE;[Uds]*[Tarifa])
RETURN 
calculo

Nota el detalle en ambas variables:
"Vendedor";[Vendedor]&"";
"Uds"; [Unidades vendidas]+0
"Tarifa"; [Tarifa]+0

Esta 'sencilla' acción permite el reconocimiento, por parte de NATURALINNERJOIN de los campos comunes... y el éxito de nuestro trabajo!!!.

Seguir leyendo...

Power Pivot: DATATABLE y crea tus tablas estáticas

Muchas veces echamos de menos la facilidad que nos da Power BI vs Power Pivot en Excel a la hora de trabajar... en muchos y diferentes aspectos (nadie diría que que el inicio de todo esto fue Power Pivot en nuestra estimada hoja de cálculo :'(

Uno de estos puntos diferenciador es la creación de tablas estáticas dentro del modelo de datos, cuando en Power BI basta presionar un botón y rellenar las columnas y registros, en Power Pivot la cosa puede llegar a ser algo más complicada.

Por supuesto, en primer lugar tenemos la ventaja de poder copiar y pegar un rango desde nuestra hoja de cálculo.
La acción es simple, seleccionamos un rango en la hoja de cálculo (preferiblemente que tenga forma de tabla o, mucho mejor, lo sea).
Copiamos lo seleccionado y vamos al editor de Power Pivot, desde la ficha Inicio > botón Pegar, lo que abrirá una ventana para configurar lo copiado (definir Nombre de la tabla, o si hay encabezados o no).

Una alternativa sería crear la tabla dentro del editor de Power Query, empleando un poco de lenguaje M, para finalmente cargarlo en el Modelo de datos.
Revisa esta publicación si quieres saber más...

Y una posibilidad más, entre otras, es emplear la función DAX llamada DATATABLE:
DATATABLE(nombre_columna1; tipo_dato1; [, nombre_columna2; tipo_dato2; [; … ] ]; datos)
la cual genera una Tabla compuesta de las distintas columnas definidas.

Los tipos de datos permitidos son: INTEGER, DOUBLE, CURRENCY, STRING, BOOLEAN, DATETIME

Un ejemplo desarrollado dentro de DAX Studio, en una ventana de consulta, donde escribimos:

EVALUATE
VAR tbl1=DATATABLE ("Categoría", STRING,"Descuento", INTEGER,{{"A","1"} ,{"B","2"},{"C","3"},{"D","11"},{"E","22"},{"F","33"}})
RETURN	
tbl1

Código que nos genera una tabla de dos columnas y seis registros.

Son tablas 100% 'operables', por lo que podemos incluso relacionarlas mediante funciones del tipo CROSSJOIN, NATURALINNERJOIN, etc.
Por ejemplo

EVALUATE
VAR tbl1=DATATABLE ("Categoría", STRING,
					"Descuento", INTEGER,
					{{"A","1"} ,{"B","2"},{"C","3"},{"D","11"},{"E","22"},{"F","33"}})
var tbl2=DATATABLE ("Categoría", STRING,
					"Precio", DOUBLE,
					{{"A","1,1"} ,{"B","2,2"},{"C","3,3"},{"D","112"},{"E","222"},{"F","333"}})

var tbl12=NATURALINNERJOIN(tbl1,tbl2)
RETURN	

{SUMX(tbl12, [Descuento]*[Precio])}

Teniendo disponible cualquier cálculo o acción que realizaríamos sobre una tabla 'normal'...
La sintáxis de creación es bastante similar a la empleada en Power Query (o incluso en Office Scripts)... pero no deja de ser bastante tediosa.
:-(

Disponemos de una alternativa algo más eficiente (tampoco para 'tirar cohetes'), que es empleando el 'constructor de tablas'
Una importante limitación es que no podemos nombrar las columnas, las cuales se autonumeran: Value1, Value2, ....
Por ejemplo, dentro del editor de DAX Studio:

EVALUATE
{("A","1") ,("B","2"),("C","3")}

que devuelve:

Un aspecto cuanto menos curioso el de crear tablas estáticas ;-)

Seguir leyendo...

Power Pivot: Tablas como VAR

Hoy veremos como en Power Pivot-DAX podemos asignar tablas como variables, y cómo trabajar con dichas tablas.

Tiempo atrás publiqué algunos conceptos importantes sobre la definición de variables (con VAR) que deberías releer ;-)

En la entrada de hoy veremos un par de ejemplos de cómo conseguir crear nuestras 'tablas temporales' (cargadas como variables), y trabajar sobre ellas como con 'cualquier otra tabla física' (con las limitaciones que en sí conlleva este método.).

Partiremos de un conjunto de tres tablas en una hoja de cálculo ('TblDATOS','TblPRECIOS' y 'TblDESC'). Tablas que habremos cargado al Modelo de datos (desde la Ficha Power Pivot > botón Agregar al modelo de datos).
Para a continuación relacionar entre sí, desde la ventana de Vista de diagrama ya dentro del Editor de Power Pivot:

Fundamental siempre definir las relaciones existentes correctamente!!.

Crearemos a continuación algunos ejemplos similares donde conseguimos segmentar y calcular únicamente las ventas brutas del vendedor 'JUAN' a los clientes definidos como 'VIP'.
Hasta llegar a un informe de tabla dinámica como el que sigue:

Primer ejemplo. Añadimos una medida con la siguiente fórmula:

 PrimerIntento:=
VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan" )
RETURN
SUMX(filtroVIP_Juan; [Unidades vendidas]*RELATED(TblPRECIOS[precio]))

En la primera asignación de variable 'filtroVIP_Juan' con VAR, cargamos la tabla devuelta con FILTER. Esta función nos devuelve como resultado una tabla completa, con las filas resultantes de aplicar el filtro sencillo o múltiple.
Posteriormente iteramos con SUMX las filas de nuestra 'tabla temporal', multiplicando las Unidades por sus precios relacionados, acumulando dicho producto.

Un caso sencillo 👍

Vamos con un segundo ejemplo un poco más elaborado...
Creamos una nueva medida con la fórmula:

testVAR:=
VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan")	--22405 reg
VAR FIN=NATURALINNERJOIN(filtroVIP_Juan;TblPRECIOS)		--22405 reg

VAR Vtas_EUR=SUMX(FIN;[Unidades vendidas]*[precio])		--recorremos las 22405 filas
RETURN
Vtas_EUR

Analicemos la fórmula.
La primera asignación de variable 'filtroVIP_Juan' con VAR cumple las mismas expectativas que el caso anterior...
En este caso, hacemos uso de la tabla temporal para con la función NATURALINNERJOIN, obtener una nueva tabla.
VAR FIN=NATURALINNERJOIN(filtroVIP_Juan;TblPRECIOS)
NATURALINNERJOIN que devuelve una nueva tabla resultante de'combinar' la primera (tabla izquierda) con la segunda (tabla derecha), incluyendo solo filas para las cuales los valores en las columnas comunes especificadas están presentes en ambas tablas.
La tabla devuelta tendrá las columnas comunes de la tabla de la izquierda y otras columnas de ambas tablas.
Obtenemos una tabla con las columnas necesarias para aplicar el siguiente cálculo:
VAR Vtas_EUR=SUMX(FIN;[Unidades vendidas]*[precio])
donde de nuevo hacemos referencia a una tabla temporal... y como expresión, que itera por esa 'tabla', utilizamos campos de ésta!!.

Último ejemplo, con el mismo objetivo de cálculo.
Nueva medida con fórmula:

TESTvar3:=VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan")		--22405 reg
VAR tbl=SUMMARIZE(filtroVIP_Juan;  TblDATOS[artículo];TblPRECIOS[precio])	--10 reg
VAR Vtas_EUR=SUMX(tbl;
			SUMX(CALCULATETABLE(TblDATOS;TblDATOS[Tipo venta]="VIP" ; TblDATOS[Vendedor]="Juan");
			TblDATOS[Unidades vendidas]*tblPRECIOS[precio]))
//22405 x 10 = 224.050 iteraciones/cálculos
RETURN
	Vtas_EUR

Otros dos intentos más con medidas...

Otro1:=VAR filtroVIP_Juan=FILTER(TblDATOS;TblDATOS[Tipo venta]="VIP" && TblDATOS[Vendedor]="Juan" )
RETURN
SUMX(filtroVIP_Juan; [Unidades vendidas]*RELATED(TblPRECIOS[precio]))

Otro2:=VAR filtroVIP_Juan=CALCULATETABLE(TblDATOS;TblDATOS[Tipo venta]="VIP" ; TblDATOS[Vendedor]="Juan")	--22405 reg
RETURN
SUMX(ADDCOLUMNS(filtroVIP_Juan; "Precio"; RELATED(TblPRECIOS[precio]));
			[unidades vendidas]*[Precio])

Como se puede comprobar, la versatilidad de esta tablas temporales es muy alta.. pero OJO!!!, no siempre podrás utilizarlas...
Funciones como ALL, ALLEXCEPT, ... requieren de 'tablas reales-existentes' para su correcto funcionamiento.

Un ejemplo de asignación de tabla como variable fallido:

Fallido:=VAR tbl=FILTER(TblDATOS; TblDATOS[Tipo venta]="VIP")
VAR FIN = CALCULATE([Valor_Eur]; ALLEXCEPT(tbl; tbl[Vendedor]))
//VAR FIN2=CALCULATE([Valor_Eur];ALLEXCEPT(TblDATOS;TblDATOS[Tipo venta]))	--sí funciona al usar Tablas existentes
RETURN
FIN

Seguir leyendo...

Power Pivot: Optimizar tiempos de cálculo

Hoy veremos cómo el complemento DAX Studio nos puede ayudar a entender y luego mejorar nuestros tiempos de cálculo respecto de las medidas y/o columnas calculadas.
Repasaremos dentro del complemento dos conceptos comentados en una entrada anterior: SE y FE (motor de almacenamiento y motor de procesamiento).

Para mostrar los diferente tiempos empleados, partiremos de un ejemplo sencillo con tres tablas:
TblDATOS (campos: 'Vendedor', 'artículo', 'Tipo venta' y 'Unidades vendidas') y 200.000 registros
TblPRECIOS (campos: 'artículo', 'precio' y 'Descuento_ART') y 15 registros
TblDESC_VIP (campos: 'Tipo' y 'Descuento_VIP') y 3 registros

Esto es, clásico ejemplo para calcular nuestras ventas netas. Comenzaremos Agregando las tres tablas al Modelo de datos de Power Pivot en nuestro libro de Excel.
Desde la Ficha de Power Pivot y presionando el botón 'Agregar al modelo de datos'.

Ya dentro del editor de Power Pivot, accederemos a la Vista de Diagrama donde arrastraremos los campos comunes entre las tres tablas, creando así las relaciones necesarias:

Vamos con los cálculos y sus medidas de tiempo...

Empezaremos con una columna calculada que nos devuelva fila a fila el cálculo requerido:
unidades x precio x (1-descuento artículo) x (1 - descuento vip)

Veamos este caso dentro del complemento DAX Studio.
Obvio que ya lo tienes descargado e instalado y disponible en la Ficha complementos de Excel.

Al presionar el botón de DAX Studio se nos abrirá el enlace de conexión a nuestro libro de trabajo:

Y se nos abrirá una ventana de código donde escribir nuestra estructura de DAX...

En la ventana escribiremos lo siguiente para replicar el efecto de crear una columna calculada en el modelo de datos:

EVALUATE
//añadimos una nueva columna a la TblDATOS
ADDCOLUMNS(TblDATOS,
		"Precio_descontado",	-- como producto de las unidades x precio x descto_vip x desc_art
		TblDATOS[Unidades vendidas]*
		RELATED(TblPRECIOS[precio])*
		(1-RELATED(TblDESC[Descuento_VIP]))*
		(1-RELATED(TblPRECIOS[Descuento_ART])))

Activaremos el Server Timings en la Ficha 'Home' dentro de DAX y a continuación ejecutaremos la consulta creada (también puedes presionar F5).
Esto iniciará unos contadores de tiempos y algunas estadísticas sobre la ejecución de la consulta.
Una vez finalizado el proceso no olvides presionar de nuevo el botón de Server Timings con el fin de parar los contadores...
En este caso, nuestra consulta de creación de columna calculada, nos reporta estos datos:

Veamos los datos...
Tiempo total en milisegundos: 5.203 ms (esto son 5,2 segundos... lo que no está nada mal... recuerda son 200.000 registros calculando datos a partir de otras dos tablas!!).
Además nos dice cuál ha sido el consumo de motores (SE y FE). En este caso todo ha sido motor de procesamiento!!.
Pero espera... todos los manuales nos dicen que crear columnas consume más motor de almacenamiento??.
Bueno, recuerda lo que decíamos en un artículo previo, mejor compruébalo... no des nada por sentado ;-)
También interesante (si conoces algo de SQL) es que esta ventana nos ofrece el código SQL que hay detrás de nuestra consulta.... pero eso es otra guerra.

Como resumen nos quedamos que tardamos 5,2 segundos en crear una nueva columna con 200.000 nuevos cálculos.

Y qué pasa si creamos una medida que replique el cálculo de esa columna??. Veámoslo.
En DAX añadimos una nueva ventana para otra query, donde añadiremos el siguiente código DAX:

DEFINE --definimos el cálculo de una medida
MEASURE TblDATOS[PrecioDescontado1]=	--con una doble iteración
SUMX(TblDESC,		--primero recorremos la tblDESC
	SUMX(TblPRECIOS,	--y para cada valor de la tblDESC, recorremos cada fila de tblPRECIOS
		VAR descVIP=TblDESC[Descuento_VIP]
		VAR descART=TblPRECIOS[Descuento_ART]
		VAR EUR=[Valor_Eur]		--además en cada ocasión exigimos se ejecute otra medida (con sus itereaciones)
		RETURN
		EUR*(1-descVIP)*(1-descART)	)	)
		
EVALUATE
//creamos un resumen simple donde se opera con nuestra medida
//se trata de no interferir demasiado en el cálculo
SUMMARIZECOLUMNS(TblDATOS[Vendedor],
				"importe_calculado", [PrecioDescontado1])

Repetimos el proceso para conocer las estadíticas de esta consulta... Y obtenemos lo siguiente:

Veamos los datos...
Tiempo total en milisegundos: 11 ms (sin comentarios :OO ).
Consumo de motores (FE: 4 ms y SE:7 ms )
Poco ahi que decir... sin embargo, el procedimiento empleado no parece el más óptimo, si nuestras tablas de dimensiones fueran de mayor envergadura, ya que dobles iteraciones nunca son buenas, puesto que exigen cálculos y repeticiones exponenciales, por ejemplo, un doble bucle de 200.000 registros, sobre una segunda tabla de 1.000 registros, exigiría 200.000 x 1.000 = 200.000.000 de cálculos!!

Un truco que aprendí de mi colega Alberto Ferrari (de SQLBI) viendo alguno de sus vídeos, es resumir a valores únicos los datos, para evitar una 'granularidad' alta (que haya muchos datos diferentes a evaluar).
Podríamos entonces emplear SUMMARIZE para tener una 'tabla reducida' a partir de la cual trabajar.
Vamonos de nuevo a DAX y creamos una nueva ventana de código, donde insertaremos un nuevo código.
La idea es la misma de antes... primero definimos una medida, para luego aplicarla en forma de resumen.
El código DAX en esta ocasión es:

DEFINE MEASURE 
TblDATOS[Importe_descontado]=		--definimos el cálculo de una medida
SUMX(
	ADDCOLUMNS(
			//summarize nos devuelve una tabla de tres columnas, con las combinaciones únicas
			//generadas entre los elementos únicos de esas columnas
			//en lugar de trabajar sobre los 200.000 registros!!
			SUMMARIZE(TblDATOS, TblDESC[Descuento_VIP],TblPRECIOS[Descuento_ART],TblDATOS[Vendedor]), 
				"IMPORTE",[Valor_Eur]),	-- al resultado anterior le añadimos una columna con la medida Valor_eur
	[IMPORTE]*(1-[Descuento_VIP])*(1-[Descuento_ART]))	--sobre la tabla anterior aplicamos una iteración
														--con SUMX acumulando valores con la expresión indicada

EVALUATE
SUMMARIZECOLUMNS(TblDATOS[Vendedor],
				"importe_calculado", [Importe_descontado])

Repetimos una vez más el proceso de controlar tiempos con el botón Server Timings y vemos lo siguiente:

O sea tenemos los siguientes valores:
Tiempo total en milisegundos: 3 ms (:OOOOO cómooooo ).
Consumo de motores (FE: 3 ms y SE:3 ms )

Bueno, no nos debería sorprender... era lo esperado. Al evitar esos dobles-triples bucles, los tiempos deben ser menores.. si además reducimos los datos sobre los que trabajar, aún más...
Aún así, brutal la enseñanza de Alberto Ferrari.. gracias!!.

Solo hemos visto algunos de los aspecto relevantes de esas estadísticas, pero hay otros puntos igualmente importantes, como el número de subconsultas que se generan en los procesos de cálculo (SE queries), o la caché, etc...

Ya comprobado cual es la mejor opción, nos iremos al editor de Power Pivot e implantaremos la medida elegida.
En el área de cálculo, añadimos la medida:

Precio_descontado2:=SUMX(ADDCOLUMNS(SUMMARIZE(TblDATOS; TblDESC[Descuento_VIP];TblPRECIOS[Descuento_ART];TblDATOS[Vendedor]); 
				"IMPORTE";[Valor_Eur]);
	[IMPORTE]*(1-[Descuento_VIP])*(1-[Descuento_ART]))

Ya podríamos usar en nuestra hoja de cálculo, una tabla dinámica asociada al modelo de datos, que mostrara el cálculo que queríamos para cada vendedor...

Seguir leyendo...

Power Pivot: Columnas calculadas o Medidas

Si algo caracteriza el trabajo con el modelo de datos-DAX (bien en Power BI bien en Power Pivot) es la eterna cuestión de qué manera de trabajar es la recomendada: Columnas calculadas o Medidas.

La respuesta rápida, y habitual, es mejor las medidas... pero como todo en la vida, no hay certezas absolutas.

Si bien es cierto que las medidas , puesto que no se almacenan en memoria, generalmente son más rápidas, es importante conocer y evaluar el equilibrio entre utilizar el 'almacenamiento en memoria' o el motor de procesamiento' que consumimos al utilizar una medida o una columna calculada.

Estos dos conceptos son interesantes, y te recomendaría echaras un vistazo a este gran video.

Estos dos motores SE (motor de almacenamiento) y FE (motor de procesamiento o formulación), que se pueden analizar fácilmente con ese magnifico complemente DAX Studio (https://daxstudio.org/), serán la clave y la respuesta real, en cada caso concreto de qué es más óptimo.
No voy a intentar definir o profundizar por ahora más en este tema... Nos quedamos con la idea que existen dos motores, y cada uno sirve y se emplea para una cosa...

Grosso modo, las columnas calculadas son buenas porque exigen poca memoria (FE) cuando generamos nuestras consultas o informes. Sin embargo, por contra, ocupan más espacio de almacenamiento (SE) en la base de datos.

De otra parte, las medidas se crean, cada vez, en el instante que se ejecuta la consulta (más FE), por lo que no quedan almacenadas en la base de datos (menos SE).

La recomendación general sería por tanto:
Medidas - cuando necesitemos calcular agregados/acumulados, i.e., cálculos más complejos.
columnas calculadas - cuando tengamos que evaluar información por filas, i.e., procesos más rutinarios.

Veamos un ejemplo comparando un cálculo similar realizado en una columna calculada, y otro mediante una medida.
Disponemos de tres tablas en nuestra hoja de cálculo

Cargaremos directamente al Modelo de datos las tres tablas (podemos usar desde la ficha Power Pivot el botón Agregr al modelo de datos).
Una vez en el modelo procedemos, desde la vista de diagrama a relacionar la tabla principal (tabla de hechos), con la tabla auxiliar (o dimensiones) de precios mediante el campo común de 'artículo'

La tabla de categorías según el escalado de unidades vendidas NO la relacionaremos... optando por realizar una búsqueda por aproximación dentro del modelo
Para esto, siguiendo recomendaciones previas, y ya que requerimos evaluar fila a fila el número de unidades vendidas, crearemos una columna calculada con la siguiente fórmula:

=CALCULATE(MIN(TblCAT[Categoría]);FILTER(TblCAT;TblCAT[de]<=TblDATOS[Ventas_Año(UDS)] && TblDATOS[VIP]=""))

con la fórmula recuperamos la categoría mínima (fíjate que están ordenadas en la TblCAT en descendente!!; si estuvieran en ascendente usaríamos MAX).
Recuperaremos la categoría mínima cuando se den al mismo tiempo las dos condiciones expresadas con la función FILTER. Lo que nos devuelve:

El siguiente cálculo lo haremos empleando una medida, ya que responderá a un agregado por suma del valor resultante de multiplicar unidades por precios. Nuestra fórmula:

Valor_Eur:=SUMX(TblDATOS;TblDATOS[Ventas_Año(UDS)]*RELATED(TblPRECIOS[precio]))

clásica fórmula de SUMX que se apoya con RELATED en datos de otra tabla previamente relacionada!!

Con los cálculos y relaciones ya ejecutadas, llegamos al momento de comparación.
Vamos a realizar un cálculo con el que obtener, en nuestro informe de tabla dinámica, por cada categoría y producto el acumulado total de cada categoría... lo vemos en la imagen siguiente:

Veamos el cálculo correspondiente a la columna calculada, que llamaré 'Acum_Ventas_byCAT':

=VAR ventas=CALCULATE([Valor_Eur];ALLEXCEPT(TblDATOS;TblDATOS[Categoría]))
RETURN
IF(TblDATOS[Categoría]="";0;ventas)

La fórmula opera sobre la medida anterior creada, obviando los posibles filtros aplicados a excepción de los aplicados sobre el campo CATEGORIA. Es decir, opera (suma agregada que nos devolvía la medida 'Valor_eur') fila por fila y retorna el dato acumulado correspondiente a la CATEGORIA de esa fila...

De otra parte replicamos el cálculo similar al anterior pero en forma de medida. La medida la llamaré: 'Suma_por_Categoria':

Suma_por_Categoria:=CALCULATE(
              SUMX( FILTER(TblDATOS;[Categoría]<>"");[Valor_Eur]);
              ALLEXCEPT( TblDATOS;TblDATOS[Categoría] ) )

donde de forma similar trabajamos sobre aquellos elementos filtrados donde exista CATEGORIA, acumulando su 'Valor_eur', con la misma excepción de filtros... esto es manteniendo aplicado la CATEGORIA, para que precisamente acumule por ese concepto.

Podemos crear nuestro informe de tabla dinámica de acuerdo a la imagen siguiente:

Observamos como a nivel de combinación Artículo y Categoría obtenemos iguales resultados, pero a nivel de subtotal por categoría la cosa cambia.
La columna calculada acumula sus parciales, como cualquier Subtotal...
Mientras que la medida devuelve el mismo dato que sus parciales... como haría un campo calculado estándar.

No nos importa para el caso... si empleando DAX Studio realizaramos un análisis de rendimiento veríamos con sorpresa que en este caso es más eficiente la columna calculada!!
Ya veremos el proceso en otro artículo.

Esto es muy interesante por que demuestra que dependiendo de la tipología del cálculo a realizar es más óptimo un motor u otro (SE frente a FE), es decir, columnas calculadas versus medidas.

Seguir leyendo...

Nombres definidos ocultos

Hoy hablaremos de un problema con el que me he enfrentado recientemente, donde cuando trataba de guardar hojas de un libro Excel me advertía de la existencia de Funciones-macro 4.0 (que no eran visibles y desde luego yo no había creado!!!)

Un colega, Charles Willians, me recomendó usar el complemento de Jan Karel Pieterse, en el cual colaboró.
Su complemento/Add-in 'Name manager' lo puedes encontrar en: https://jkp-ads.com/excel-name-manager.asp

Pero sobre todo me dió la pista por donde empezar... 'busca nombres definidos ocultos'.
Me acordé entonces de una publicación de hace muchos años atrás (Ocultar y Mostrar los nombres definidos) que resolvería el problema.

Así pues añadí la macro siguiente en un módulo del libro 'problemático':

Sub Localiza_Nombres_Ocultos()
Dim ndNombreDef As Name

For Each ndNombreDef In ThisWorkbook.Names
    If ndNombreDef.Visible = False Then
        If InStr(1, ndNombreDef.Name, "_xl") > 0 Then
            Debug.Print ndNombreDef.Name & "_" & ndNombreDef.Value
            ndNombreDef.Visible = True   ' para hacerlo visible...
         End If
    End If
Next ndNombreDef

End Sub

La sorpresa es el listado de 'nombres ocultos' que aparecieron listados!!
_xlfn._xlws.FILTER_=#NAME?
_xlfn.ANCHORARRAY_=#NAME?
_xlfn.CONCAT_=#NAME?
_xlfn.COUNTIFS_=#NAME?
_xlfn.IFERROR_=#NAME?
_xlfn.SUMIFS_=#NAME?
_xlfn.TEXTJOIN_=#NAME?
_xlfn.UNIQUE_=#NAME?
_xlpm.miFuncion1_=#NAME?
_xlpm.miFuncion2_=#NAME?
_xlpm.miFuncion3_=#NAME?

¿Qué son esos prefijos (_xlfn. _xlws o _xlpm)?.

El primero: _xlfn es el clásico prefijo que aparece cuando una función está trabajando en una versión previa, donde no existía.
Por ejemplo, si habrimos la función SUMAR.SI.CONJUNTO (SUMIFS) en una versión de Excel 2007, Excel mostrará en las celdas donde aparezcan dichas funciones, ese prefijo... además del valor resultado.

El segundo prefijo que vemos es _xlws que identifica algunas de las nuevas/últimas funciones lanzadas por Microsoft (FILTRAR, ORDENAR, ....), y con idéntico significado.

El tercer prefijo que aparece es _xlpm que nos refiere a las funciones LAMBDA, que hayamos podido generar...

Por todo lo leído en diferente documentación, parece ser este fallo se puede producir al abrir el archivo de trabajo con versiones anteriores.

Por suerte, una vez identificado ese listado, y lanzada la macro anterior, ya tendríamos a nuestra disposición en el Adminstrador de nombres definidos dichos nombres que deberemos eliminar.
Una vez borrados el problema desaparece!!!

También podríamos haber añadido en la programación de la macro la acción .Delete sobre el objeto ndNombreDef.

Cómo exactamente ha ocurrido esto y por que los identifica como funciones macro 4.0 es dificil de entender... pero al menos tenemos una solución (al menos en mi caso sirvió).

Un punto importante para comenzar a revisar un fichero, cuando algo empieza a fallar en él es revisar el Inspector de documentos.
Accederemos a esta herramienta desde la ficha Archivo > Información > Inspeccionar libro

Observamos un listado de utilidades, herramientas, funciones, etc. usadas en el libro presente!!!
Por ejemplo, fíjate en la imagen anterior (corresponde en parte al fichero problemático del ejemplo), como aparece listado 'Nombres ocultos'.

Sin duda una primera aproximación al problema... pero, si además presionamos el botón desplegable de 'Comprobar si hay problemas' > 'Inspeccionar documento', nos surgirá una ventana donde optar por analizar diferentes puntos de un libro:
Tablas dinámicas y segmentaciones
Gráficos dinámicos
Fórmulas de cubo
Escalas de tiempo (en caché)
Datos XML personalizados
Comentarios y anotaciones
Propiedades del documento e información personal
Encabezados y pies de página
Filas y columnas ocultas
Nombres y hojas de cálculo ocultos
Contenido invisible
Funciones de datos y vínculos externos
Encuestas de Excel
Propiedades de la hoja de cálculo personalizadas
(entre otras...)

Al Aceptar (botón Inspeccionar) los términos de ese listado del Inspector obtenemos unos resultados o validaciones en dicha ventana del Inspector del libro, donde decidiremos qué hacer:

Una herramienta muy útil y sobre todo sencilla, que nos permite empezar a buscar problemas...

Seguir leyendo...

Anexar todas las tablas de un libro con funciones

Un nuevo ejemplo de potencial de Excel es la respuesta, empleando solo funciones, a anexar todas las tablas de un mismo libro de manera automática.

Una posible solución a este problema es emplear Power Query, como ya se publicó en este artículo del blog.

Hoy conseguiremos el mismo resultado pero empleando algunas de 'catorce funciones':
AJUSTARFILAS, DIVIDIRTEXTO o ENFILA
Junto a otras igual de potentes como:
LET, UNIRCADENAS, MAP o LAMBDA

Te recomiendo leas previamente, antes de continuar, el artículo siguiente, donde se desarrolla un ejercicio que nos servirá para alcanzar nuestra meta.

Adicionalmente deberás entender el uso de las funciones macro 4.0 (leer artículo).

El uso de una función macro 4.0 particular es fundamental.
La función NAMES - NOMBRES(nombre_documento/archivo; tipo; criterio)
La función devuelve un vector, ordenado alfabéticamente, con los nombres definidos incluidos en el libro de trabajo descrito...
Sabiendo que:
-si omitimos el primer argumento, trabajaremos sobre el libro activo.
-el argumento tipo admite tres valores:
1-solo lista 'nombres normales'
2-nombres definidos ocultos
3-todos los nombres
-el tercer argumento de criterio, que permite el uso de comodines, permite identificar los nombres a recuperar

Llegados a este punto es importante resaltar un aspecto clave...
La función macr 4.0 'NOMBRES' trabaja exclusivamente sobre nombres definidos, NO sobre tablas (tiene sentido por que las Tablas aparecieron mucho después de la existenca/descatalogación de las macro 4.0)...
Asi que para que el trabajo se realice correctamente, a cada tabla habrá que asignarle un nombre definido!!
Un pequeño precio a pagar...

Vamos con el ejercicio...Partimos de dos tablas iniciales: TblUNO y TblDOS, a las cuales he asignado sendos nombres definidos: datosUNO y datosDOS respectivamente...
Notemos el patrón tomado a la hora de asignar nombres definidos, incluyendo un prefijo igual en ambas tablas (esto es clave).

Todo comienza creando una función LAMBDA personalizada que trabaje con la función macro 4.0 'NOMBRES'. Por tanto abrimos nuestro Administrador de nombres definidos e incluimos la siguiente fórmula, renombrándola como 'fxNAMES':
=LAMBDA(criterio;NOMBRES(;3;criterio))

Si utilizamos esta función directamente en una celda, por ejemplo en H2 y escribimos:
=TRANSPONER(fxNAMES("datos*"))&T(AHORA())
tendríamos un vector vertical 'volátil' con los nombres definidos en el libro activo que comiencen por el prefijo 'datos'...

Comprobado el resultado de esta función, ya podemos montar nuestra fórmula en E2 para obtener una matriz resultante de anexar las tablas-nombres definidos deseados:

=LET(rngs;TRANSPONER(fxNAMES("datos*"))&T(AHORA());
datos;UNIRCADENAS(";";;MAP(rngs;
                           LAMBDA(tbl;" "&ENFILA(MATRIZATEXTO( INDIRECTO(tbl);0) ))));

AJUSTARFILAS(EXTRAE(DIVIDIRTEXTO(datos;;";");2;LARGO(datos));2;"-"))

Versión reducida de la comentada en el artículo arriba indicado

La fórmula recorre los elementos obtenidos con nuestra función macro 4.0 (nombres definidos que empiecen por 'datos') para descomponerlos en sus distintos elementos con MATRIZATEXTO, trasladarlo a un vector fila (con la función ENFILA).
La función MAP se encarga de recorrer los distintos vectores obtenidos con ENFILA (un vector por cada nombre definido).
Y UNIRCADENAS nos permite concatenar en un único vector todos los elementos... Será finalmente DIVIDIRTEXTO quien 'separe' esa cadena de texto en un nuevo y único vector que AJUSTARFILAS devolverá en una matriz de dos columnas.

La fórmula se podría completar incluyendo encabezados o incluso fila de totales. Te remito al artículo anterior.

Puedes comprobar que al añadir nuevas tablas: 'TblTRES' y 'TblCUATRO', habiéndoles asignados nombres definidos: datosTRES y datosCUATRO, automáticamente aparecerán anexados!!

Increible solución sin salir del entorno de la hoja de cálculo!!

Seguir leyendo...

LAMBDA y funciones macro 4.0

Sin duda las funciones macro 4.0 es una reminiscencia del pasado de Excel... incluso recientemente Microsoft decidió deshabilitarlas por defecto...
Pero siguen estando ahí presentes. Y aunque la inmensa mayoría de ellas solo tienen sentido dentro del contexto de las hojas de macros 4.0 de un libro de trabajo, algunas de ellas las podemos emplear en una hoja de cálculo estándar...
En general todas aquellas que retornan información y que habitualmente, en su nombre en inglés, contienen la palabra GET (es una generalidad, por supuesto).

Así pues, entre otras, funciones como:
GET.CELL = INDICAR.CELDA
GET.DOCUMENT = INDICAR.DOCUMENTO
GET.FORMULA = INDICAR.FORMULA
etc..
o respecto a información de tablas dinámicas:
GET.PIVOT.TABLE = INDICAR.TABLA.DINAMICA(type_num,pivot_table_name)
GET.PIVOT.FIELD = INDICAR.CAMPO.TABLA.DI(type_num, pivot_field_name, pivot_table_name)
GET.PIVOT.ITEM = INDICAR.ELEMENTO.TABLA.DI(type_num, pivot_item_name, pivot_field_name, pivot_table_name)

Sabemos que estas funciones las podemos emplear en nuestras hojas de cálculo, incluyéndolas dentro de Nombres definidos...
Un punto interesante, ya que coincide con el comportamiento o el requerimiento de las novedosas funciones LAMBDA.
Converge pasado y futuro... lo que nos abre una posibilidad de trabajo muy interesante.

Como ejemplo de lo potente de combinar funciones, veremos como facilita LAMBDA el uso de estas funciones macro 4.0.

Pongamos por caso que tenemos una tabla dinámica, llamada 'TD_UNO', de la cual me interesa recuperar cierta información. Por ejemplo:
- cuándo fue (hora, minuto y segundo) actualizada por última vez
- quién fue el usuario que hizo esa última actualización
- o cuál es la lista de campos disponibles para nuestra tabla dinámica
- o qué campos están a la vista (o cuáles ocultos/no mostrados) en la tabla dinámica
- o una lista de campos en el área de filas (o área de columnas o en el área de filtros)
- o cuál es el rango de celdas que ocupa la tabla dinámica, o el rango del área de filas/columnas/filtros
- y así hasta 22 puntos de información increible!!

Todo esto nos lo puede aportar la función:
INDICAR.TABLA.DINAMICA(type_num,pivot_table_name)
solo dos argumentos.
El primero - type_num - un valor entero de 1 a 22, cada uno muestra uno de los puntos de información comentados brevemente (es fácil de identificar qué significan la mayoría de ellos.
El segundo argumento - pivot_table_name - nos pide el nombre, entre comillas, de la tabla dinámica de la que queremos obtener información...

Si trabajamos la función bajo el paraguas de LAMBDA, podríamos definir la siguiente función:
=LAMBDA(tipo;INDICAR.TABLA.DINAMICA(tipo;"TD_UNO"))
para luego incluirla en el Administrador de nombres asignándole un nombre de función personalizado.

Puedes ver el resultado en la imagen siguiente.... donde en la celda G3:G24 añadimos nuestra función Lamnda recién creada: =fxPT(F23)

El significado de cada tipo sería pues:

INDICAR.TABLA.DINAMICA(type_num,pivot_table_name)		
1		último usuario que actualizá la TD
2		fecha última actualización
3		campos disponibles en la TD
4		número de campos disponibles
5		campos mostrados
6		campos no mostrados
7		campos en el área de filas
8		campos en el área de columnas
9		campos en el área de filtros
10		campos en el área de valores
11		rango sin incluir área de filtros
12		rango incluyendo el área de filtro
13		rango del área de filas
14		rango del área de columnas
15		celda que define la TD
16		rango del área de filtros
17		rango del área de valores
18		Verdadero si muestra Totales por fila
19		Verdadero si muestra Totales por columna
20		Verdadero si se guardan los datos con la TD
21		Verdadero si está habilitado el Autoajuste de columnas
22		fuente de la TD

Increible función!!...
Pues espera, tenemos también:
INDICAR.CAMPO.TABLA.DI(type_num, pivot_field_name, pivot_table_name)
con 17 posibilidades de información!!!.
Si la embebemos dentro de LAMBDA tendríamos (en un nombre definido):
=LAMBDA(tipo;campo;INDICAR.CAMPO.TABLA.DI(tipo;campo;"TD_UNO"))
donde deberemos informa del tipo (entero entre 1- 17, y un campo de nuestra tabla dinámica 'TD_UNO'.

A modo de ejemplo...

Sin pretensiones de aburrir con listados interminables:

INDICAR.CAMPO.TABLA.DI(type_num, pivot_field_name, pivot_table_name)		
1		todos los elementos del campo
2		los elementos visibles del campo
3		los elementos no visibles del campo
4		área de ubicación del campo (0 = oculto / 1 = filas / 2 = Cols / 3 = filtros / 4 = valores)
5		….

Y de forma similar tendríamos:
INDICAR.ELEMENTO.TABLA.DI(type_num, pivot_item_name, pivot_field_name, pivot_table_name)
con otras 9 posibilidades.
1-posición del elemento dentro del campo
2-celda donde se ubica
3-rango donde se despliegan los valores de ese elemento
...

Quizá no tan útil como las anteriores, pero interesante conocer de su existencia.

Creo que es importante aprovecharse de lo mejor de ambos mundos (pasado y presente-futuro), y sin duda este tipo de funciones macro 4.0 ofrecen unas opciones inimaginables para la mayoría!!.

Y recuerda que solo he mostrado algunas de las funciones disponibles :OOO

Seguir leyendo...

Replica de tabla dinámica solo con funciones

Como segundo ejercicio empleando 'las catorce nuevas funciones' he optado por replicar y construir una tabla de referencia cruzada incluyendo encabezados y filas de totales generales por filas y columnas.
Otra de esas fórmulas 'un poco largas' (que seguro se pueden mejorar/optimizar) que aportan otro punto de vista en el mundo de las funciones de hoja de cálculo.

Partiremos de una tabla de información (llamada 'TblDATOS'), y con una sola fórmula lograremos:

A efectos de comprobación, en la parte inferior de la tabla resultante, una tabla dinámica que verifica que el resultado obtenido es correcto...

La fórmula desarrollada en J2 es:

=LET(encFilas;APILARV("";ORDENAR(UNICOS(TblDATOS[Delegación])));
encCols;TRANSPONER(ORDENAR(UNICOS(TblDATOS[Cliente])));
numFilas;CONTARA(encFilas);
numCols;CONTARA(encCols);
valores;EXCLUIR(ARCHIVOMAKEARRAY(numFilas;numCols;
    LAMBDA(a;b;SUMAR.SI.CONJUNTO(TblDATOS[Ventas];
                                 TblDATOS[Delegación];INDICE(encFilas;a);
                                 TblDATOS[Cliente];INDICE(encCols;1;b))));1);
TotalCols;BYCOL(valores;LAMBDA(col;SUMA(col)));
TotalFilas;BYROW(valores;LAMBDA(fila;SUMA(fila)));
TotalAbs;SUMA(TblDATOS[Ventas]);
SI.ERROR(APILARH(APILARV(encFilas;"Total general");
                 APILARV(encCols;valores;TotalCols);
                 APILARV("Total general";TotalFilas));TotalAbs))

Veamos los pasos dados...
En primer lugar obtenemos dos vectores (futuros encabezados) a partir de los campos de 'Delegación' y 'Cliente'. Dentro de la función LET definimos las variables:
encFilas;APILARV("";ORDENAR(UNICOS(TblDATOS[Delegación])));
encCols;TRANSPONER(ORDENAR(UNICOS(TblDATOS[Cliente])));

Conseguimos unos listados de elementos únicos y además ordenados...
Nota que los elementos 'clientes' quedan transpuestos para distribuirse en la primera fila de la futura tabla; y que a las 'delegaciones', mediante APILARV, le unimos una primera posición vacía (""), que corresponderá a la celda 1-1 de la tabla (donde cruzan encabezados de filas y columnas...

En un segundo paso realizamos un conteo de elementos de ambos encabezdos:
numFilas;CONTARA(encFilas);
numCols;CONTARA(encCols);

Este conteo nos servirá para definir el tamaño del 'área de valores' futuro.

En la etapa siguiente obtenemos los valores cruzados por 'delegación' y 'cliente'...
valores;EXCLUIR(ARCHIVOMAKEARRAY(numFilas;numCols;LAMBDA(a;b;SUMAR.SI.CONJUNTO(TblDATOS[Ventas];TblDATOS[Delegación];INDICE(encFilas;a);TblDATOS[Cliente];INDICE(encCols;1;b))));1);
La función ARCHIVOMAKEARRAY se encarga de recorrer una matriz ficticia de la dimensión (filas x columnas) adecuada... sustituyendo cada elemento de la matriz por la suma acumulada (SUMAR.SI.CONJUNTO) del cruce de 'delegación' y 'cliente' correspondiente.
La función EXCLUIR nos facilita eliminar una primera fila innecesaria.

A continuación construimos los dos vectores de valores para los totales generales (filas y columnas):
TotalCols;BYCOL(valores;LAMBDA(col;SUMA(col)));
TotalFilas;BYROW(valores;LAMBDA(fila;SUMA(fila)));

Las funciones BYROW y BYCOL nos permiten recorrer cada fila/columna de nuestra recién creada matriz de valores para acumular por cada elemento...

Una última definición de variable corresponde al Total absoluto, la suma total, cruce de los Totales 'generales':
TotalAbs;SUMA(TblDATOS[Ventas]);
simplemente sumamos el campo de 'Ventas' de la tabla fuente...

Con todas las variables definidas estamos en disposición de proceder al montaje de nuestra tabla de referencia cruzada dinámica. Así, concluimos nuestra función LET con el cálculo:
SI.ERROR(APILARH(APILARV(encFilas;"Total general");APILARV(encCols;valores;TotalCols);APILARV("Total general";TotalFilas));TotalAbs)
conde con una secuencia adecuada de APILARH y APILARV podemos construir y montar nuestro pequeño Frankstein.

Quizá existan formas más elegantes, pero creo que como lógica de trabajo es una manera válida de conseguir nuestra meta.

Seguir leyendo...