Power Pivot: PREVIOUSYEAR Inteligencia de tiempo en DAX

Tiempo atrás expuse como obtener a partir de nuestro modelo de datos en Power Pivot, y con expresiones MDX y el uso de funciones CUBO (ver) una comparativa del año presente respecto al anterior.

Hoy veremos como las funciones de inteligencia de tiempo en DAX nos pueden ayudar a conseguir ese mismo resultado...

Rapidamente recordaremos las funciones CUBO empleadas en el ejemplo:
- en primer lugar definimos dos cubos de elementos (para años y para comerciales):
En la celda K3: =CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2017]";
"Fechas-13-17")

En la celda L2: =CONJUNTOCUBO("ThisWorkbookDataModel";
"{[Tabla1].[Comercial].&[ANA],[Tabla1].[Comercial].&[EVA]}";
"ana+eva")
Por otra parte desplegamos los elementos contenidos en Años en la celda K4:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";
$K$3;
SECUENCIA(RECUENTOCONJUNTOCUBO($K$3)))

Y finalmente los cálculos agregados para el año corriente en L4:L8:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";K4;$L$2)
Para el años previo en M4:M8
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";"[Calendar].[Date Hierarchy].&["&$K4&"].Lag(1)";$L$2)
donde la expresión .Lag(1) nos trasladaba al periodo anterior de referencia.
Por último, el cálculo acumulado de Todos los comerciales en los años de estudio. En N4:N8
=VALORCUBO("ThisWorkbookDataModel";
"[Measures].[Sum_Ventas]";
K4;
"[Tabla1].[Comercial].&["&MIEMBRORANGOCUBO("ThisWorkbookDataModel";$L$2;1)&"].Parent")

Donde la expresión .Parent recupera el nivel superior del conjunto seleccionado...

Funciones ya explicadas en el post 'linkado'...

Vayamos ahora a Power Pivot a escribir algo de DAX.
Por supuesto, teníamos cargada nuestra tabla en el Modelo de datos, y habíamos generado una Tabla de fechas, relacionando ambas tablas...

A continuación iremos al área de cálculo de la Tabla de datos e insertaremos las siguientes tres medidas:

Actual:=CALCULATE(sum([Ventas]);		
		FILTER(tabla1; (Tabla1[Comercial] IN {"ANA";"EVA"} ));
		FILTER('Calendar'; ('Calendar'[Year] >=2013 && 'Calendar'[Year]<=2017)))
		
Y-1:=CALCULATE(sum(Tabla1[Ventas]);		
		PREVIOUSYEAR('Calendar'[Date]);
		(Tabla1[Comercial] IN {"ANA";"EVA"});
		('Calendar'[Year] >=2012 && 'Calendar'[Year]<=2017))
		
TodosComerciales:=CALCULATE(sum([Ventas]);		
		FILTER('Calendar'; ('Calendar'[Year] >=2013 && 'Calendar'[Year]<=2017)))

Las tres medidas se basan en la misma idea: Acumular 'Ventas' al cumplirse múltiples condiciones...
Interesante siempre la manera de acotar los acumulados con distintos FILTROS aplicados...

En el caso de las ventas del año anterior, hacemos uso de la función PREVIOUSYEAR que nos devuelve una tabla que contiene una columna de todas las fechas del año anterior, en función de la última fecha de la columna de fechas del contexto actual.

Con nuestras medidas montadas las trasladamos a nuestra Tabla dinámica del Modelo de datos:

Por supuesto los resultados son iguales en ambos cálculos: DAX vs MDX.

Es interesante ver como al aplicar los distintos filtros en las medidas, afecta a los elementos visibles en la tabla dinámica..
Fíjate por ejemplo en el cuadro de segmentación de los años (traido de la 'tabla de fechas'), donde solo aparecen los años filtrados...
O si quitáramos la medida de 'TodosComerciales', solo veríamos los elementos del filtro: ANA y EVA:

Nuevamente entender correctamente el funcionamiento de los contextos de fila y filtro es fundamental. Saber que, a grandes rasgos, se diferencian en que el contexto de fila representa la "fila actual", y se utiliza para evaluar las fórmulas y expresiones de una columna calculada que usan los iteradores de la tabla.
Mientras que el contexto de filtro se usa para evaluar las medidas, y representa los filtros que se aplican directamente a las columnas y filtros del modelo que propagan las relaciones del modelo (i.e, bien en el modelo, bien en la hoja de cálculo).

Seguir leyendo...

Power Pivot-DAX: Medias Ponderadas Parciales y Totales

En la entrada anterior del blog hablamos sobre la función DAX: HASONEVALUE, interesante función que identificaba situaciones únicas, esto es, donde en el contexto de filtro aparecía una única vez un elemento determinado.

Hoy aplicaremos un caso en Power Pivot para procesar un cálculo de precio medio ponderado parcial o total según la coincidencia o no de ese elemento único.
La idea es obtener el Precio Medio Ponderado Parcial cuando el número de clientes para un artículo es mayor que uno, y en caso que el artículo solo se haya vendido a un único cliente, tomar el Precio Medio Ponderado General (de todas las ventas)

Partiremos de dos tablas cargadas en nuestro modelo de datos:
-Tabla Ventas (Tabla1)
-Tabla Precios (TblPRECIOS)
Ambas relacionadas por el código de artículo (por ejemplo desde la Vista de diagrama del editor de Power Pivot).

Y dentro de la Tabla de ventas ('Tabla1') en su área de cálculo incorporamos las siguientes medidas:

Total_Unidades:=SUM(Tabla1[Unidades])						
Total_Ventas:=SUMX(Tabla1;Tabla1[Unidades]*RELATED(TblPRECIOS[Precio_Unitario]))						
IF_HOV2:=IF(HASONEVALUE(Tabla1[Cliente]);
        CALCULATE(SUMX(ALL(Tabla1);[Total_Ventas])/SUMX(ALL(Tabla1);[Total_Unidades]));
        [Total_Ventas]/[Total_Unidades])

Las dos primeras medidas:
Total_Unidades:=SUM(Tabla1[Unidades])
Total_Ventas:=SUMX(Tabla1;Tabla1[Unidades]*RELATED(TblPRECIOS[Precio_Unitario]))
funcionarán en la tabla dinámica dentro del contexto de filtro aplicado, por tanto se ajustará el cálculo (en nuestro ejemplo) al artículo listado... y en su caso al Trimestre seleccionado (en realidad, a cualquier filtro de cualquier campo que se incluya en la tabla dinámica).

Mientras que la tercera medida se encarga de forzar un cálculo parcial u otro total, aplicando un condicional IF con nuestra función DAX: HASONEVALUE:
IF_HOV2:=IF(HASONEVALUE(Tabla1[Cliente]);
CALCULATE(SUMX(ALL(Tabla1);[Total_Ventas])/SUMX(ALL(Tabla1);[Total_Unidades]));
[Total_Ventas]/[Total_Unidades])

La opción de 'verdadero' de nuestro condicional, i.e., cuando exista un solo cliente, retorna el cálculo ponderado: Total_ventas / Total_Unidades... pero usando la función ALL, de la que sabemos que devuelve todas las filas de una tabla (o en su caso, todos los valores de una columna) omitimos los filtros que se puedan haber aplicado... muy útil para borrar filtros y crear cálculos en todas las filas de una tabla:
CALCULATE(SUMX(ALL(Tabla1);[Total_Ventas])/SUMX(ALL(Tabla1);[Total_Unidades]));
Nota: podríamos omitir el uso de CALCULATE... pero aporta flexibilidad en caso de que necesitemos centrarnos sobre algún elemento, y aplicar algún filtro (por ejemplo, sobre algún país).
Por tanto, indicamos aqui que suma SIEMPRE todas las ventas y todas las unidades, omitiendo cualquier filtro aplicado (en la tabla dinámica o en cualquier medida usada)... así tendremos el Precio Medio Ponderado Total

La opción de 'falso' del condicional anterior es simple, el cociente de las medidas Total_Ventas y Total_Unidades:
[Total_Ventas]/[Total_Unidades]
En este caso la interpretación es directa... el cálculo se ajusta al contexto de filtro aplicado en cada instante...

Podemos ver el resultado en nuestra tabla dinámica:

Comprobamos en la imagen como para los artículos que incluyen un único cliente: x2, x4 y x8 el dato devuelto corresponde al Promedio Total, de todos y cada una de las ventas... mientras que en el resto el cálculo responde exclusivamente a las Ventas y Unidades de ese artículo en particular...

OJO, por que al aplicar otros filtros (trimestres o países, por ejemplo), el Precio Medio Ponderado Total, se seguirá calculando sobre la TOTALIDAD de los registros.. y no sobre los que correspondan a ese filtro de País o Trimestre.
Por ejemplo, si aplicamos un filtro para el 'Trim.2' y país 'ES' veremos con el cálculo de PMP Total sigue siendo el mismo que anteriormente (0,411887925), mientras que el Total general de la tabla dinámica, muestra el valor ajustado para los filtros aplicados (Trim.2 y ES).

Seguir leyendo...

Power Pivot-DAX: HASONEVALUE

Hoy hablaremos de una de esas funciones DAX de las que todo el mundo habla, y siempre es dificil de entender: HASONEVALUE('Nombre_columna')
A priori es una función muy simple. Solo identifica con VERDADERO cuando en el contexto de filtro actual se obtiene un único (y distinto) valor. Devolverá FALSO en otro caso.
Es decir, a priori es una función Boolena (retorna VERDADERO o FALSO) donde se evalúa, en la situación de filtros aplicados en ese instante, si existe o no un único valor distinto...
Equivaldría a cualquiera de estas dos expresiones DAX:
DISTINCTCOUNT(Columna) = 1
COUNTROWS ( VALUES ( Columna ) ) = 1

Tratemos de entender su comportamiento.
Tenemos una tabla de datos cargada al Modelo de datos en Power Pivot para Excel.
A partir de la cual hemos creado una Tabla dinámica desde nuestro editor de Power Pivot.

En nuestro Modelo de datos hemos creado las siguientes medidas

CR1:=COUNTROWS(Tabla1)
DC1:=DISTINCTCOUNT(Tabla1[Cliente])
CR_V2:=COUNTROWS ( VALUES ( Tabla1[Cliente]) )
HasOneValue1:=HASONEVALUE(Tabla1[Cliente])
IF_HOV:=IF(HASONEVALUE(Tabla1[Cliente]);"un solo cliente";DISTINCTCOUNT(Tabla1[Cliente]))
HOV1:=CALCULATE(HASONEVALUE(Tabla1[Cliente]);Tabla1[Artículo] IN {"x2";"x4"})

En el área de cálculo de nuestra tabla cargada:

Y configuraremos nuestra tabla dinámica llevando, de momento el campo de 'Artículos' al área de filas, y nuestras seís medidas al área de valores...

Nos fijaremos en primer lugar en las tres medidas:

CR1:=COUNTROWS(Tabla1)
DC1:=DISTINCTCOUNT(Tabla1[Cliente])
CR_V2:=COUNTROWS ( VALUES ( Tabla1[Cliente]) )

Donde COUNTROWS realiza un conteo del total de registros incluidos en ese 'contexto de filtro actual', en nuestro caso para esos artículos en concreto nada más.
Donde DISTINCTCOUNT cuenta el número de clientes disitintos para ese artículo mostrado...
Lo que coincide con COUNTROWS ( VALUES(...)). Donde VALUES con una columna como argumento, devuelve una tabla de una columna que contiene los valores distintos de la columna especificada... esto es, los valores duplicados se quitan y solo se devuelven los valores únicos.

Interesante estas dos últimas medidas, ya que se ajustan al contexto de filtro en cada momento de nuestra tabla dinámica, para identificar resgistros distíntos o únicos...
Precisamente lo que nos interesa conocer, especialmente cuando ese conteo sea uno.

Nos fijaremos a continuación en las demás medidas:

HasOneValue1:=HASONEVALUE(Tabla1[Cliente])
IF_HOV:=IF(HASONEVALUE(Tabla1[Cliente]);"un solo cliente";DISTINCTCOUNT(Tabla1[Cliente]))
HOV1:=CALCULATE(HASONEVALUE(Tabla1[Cliente]);Tabla1[Artículo] IN {"x2";"x4"})

Lo que vemos en la tabla dinámica, en el área de valores:

La primera medida:
HasOneValue1:=HASONEVALUE(Tabla1[Cliente])
nos devuelve VERDADERO en aquellos artículos (contexto de filtro actual) donde solo existe un único Cliente, esto es, artículos: x2, x4 y x8
Fácil de ver si incluimos en el área de filas el campo 'Cliente', subordinado al 'Artículo':

Comprobamos que solo para esos tres artículos comentados (x2, x4 y x8) se cumple la 'condición' de tener un solo cliente...
OJO!!, al incluir el campo 'Cliente' en la tabla dinámica el contexto de filtro ha cambiado, y por tanto el resultado devuelto por esas medidas también... ya que bajo este supuesto de doble filtro (Artículo + Cliente), todas las combinaciones son únicas, y por tanto HASONEVALUE retorna siempre VERDADERO!!

De forma similar la segunda medida:
IF_HOV:=IF(HASONEVALUE(Tabla1[Cliente]);"un solo cliente";DISTINCTCOUNT(Tabla1[Cliente]))
donde en realidad evaluamos la misma cuestión (un solo cliente)... pero donde el valor lógico devuelto lo empleamos dentro de un condicional IF, y convertir en VERDADERO en un texto y el FALSO en un cálculo de distintos.

En la tercera medida nos aprovechamos de CALCULATE para limitar la 'búsqueda' solo a los elementos x2 y x4 (los indicados en el argumento de filtro de CALCULATE.
Podemos comprobar en la imagen siguiente, como solo para esos dos artículos obtenemos el VERDADERO:

Solo los clientes DOS y TRES contienen un único valor para los artículos x2 o x4:

El cliente UNO al no contener ningún artículo x2 o x4 devuelve FALSO...

Un último ejemplo consistiría en aplicar un nuevo filtro sobre otro campo, por ejemplo sobre los trimestres.
Comprobamos como cambia el resultado si el filtro es el Trim.1 o el Trim.2, ya que los 'clientes únicos' son unos u otros en cada caso.
Para el 'Trim.1' solo los artículos x1, x6, x8 y x9 continen un único cliente:

Mientras que en el 'Trim.2' son los artículos x2, x4, x5 y x6 los que despliegan un solo cliente...

El uso más general de esta función, en base a lo que he visto en el día a día, es controlar el cálculo del Total general... si bien, creo que su potencial (en mi opinión) pasa por discriminar el cálculo mostrado en esos casos de concurrencia única...
Cosa que veremos en ejemplos posteriores.

Seguir leyendo...

ESFECHA con fórmulas

Creo que el título del post lo dice todo.... vamos a crearnos una fórmula en nuestra hoja de cálculo para determinar si una celda es o no una fecha.
Decir, eso si, que la mejor opción es crear con un poco de programación una UDF:

Function EsFecha(celda As Range) As Boolean
    Application.Volatile
    EsFecha = VBA.IsDate(celda)
End Function

Probablemente de lo más fiable que nos encontraremos...

Sin embargo hoy buscaremos una alternativa (quizá exista alguna más) con el empleo de fórmulas... que pretende acercarse al 99% de efectividad en la detención de fechas.

Como observarás en la columna B hay un gran número de fechas con distintos tipos de formatos, predefinidos y personalizados; y algún otro tipo de dato (texto, números, errores, valores lógicos, ...) con formatos habituales.

Para detectar cuáles son fechas (sin usar macros) es frecuente emplear la función:
CELDA(tipo_de_info;ref)
que entre otros, nos permite recuperar el tipo de formato existente en una celda, de acuerdo (según la documentación de Microsoft) a la siguiente tabla:

Si el formato de Excel es	La función CELDA devuelve
Estándar	"G"
0	"F0"
#.##0	".0"
0	"F2"
#.##0,00	".2"
$#.##0_);($#.##0)	"C0"
$#.##0;(rojo)-$#.##0	"C0-"
$#.##0,00_);($#.##0,00)	"C2"
$#.##0,00;(rojo)-$#.##0,00	"C2-"
0	"P0"
0	"P2"
0	"C2"
# ?/? o # ??/??	"G"
d/m/aa o d/m/aa h:mm o dd/mm/aa	"D4"
d-mmm-aa o dd-mm-aa	"D1"
d-mmm	"D2"
mmm-aa	"D3"
mm/dd	"D5"
h:mm a.m./p.m.	"D7"
h:mm:ss a.m./p.m.	"D6"
h:mm	"D9"
h:mm:ss	"D8"

Que es la fórmula aplicada en la columna C de la imagen...:
CELDA("formato";B2)
Observarás como muchos de las celdas con fechas, y formatos no definidos en la tabla anterior, no los identifica con códigos de tipo fecha: D1,D2,D3,D4,D5...
Una pena :-(
Aunque sería válida, y fácil de usar, en muchas ocasiones...
Buscamos en todo caso algo más de precisión.

Así pués, después de darle vueltas al asunto pensé que una solución más fiable podría estar en el formato aplicado en la celda..
Y una forma de recuperar el formato de una celda sin emplear programación en VBA es el uso de las macrofunciones 4.0 de Excel, sí, esos dinosaurios aún están disponibles.
En concreto usaremos INDICAR.CELDA (GET.CELL en inglés) (ver ejemplos aquí).
En esta ocasión usaremos el parámetro 7 que nos devuelve precisamente el formato de una celda.
RECUERDA que esta función debes escribirla en un contexto de nombre definido!!
Para ello sitúate en alguna celda de la fila 2 (D2) y añade el nombre definido siguiente:

Fíjate en la fórmula añadida:
=INDICAR.CELDA(7;'Hoja1 (2)'!$B2)&SI((AHORA()*0)=0;"")
donde hemos añadido una parte 'volatil' con la función AHORA().

Si aplicamos esta fórmula en la columna D:
=FormatoCelda

En este caso, no sería necesario hacer visible en la hoja de cálculo esta fórmula, ya que la incorporaremos en nuestra fórmula final.

¿Cuál es la línea de trabajo entonces?... fijarnos en el formato de celda, sea el que sea, y buscar alguno de los tres caracteres básicos de un formato de fecha: d m a
OJO con los formatos personalizados que pueden incluir colores entre corchetes, o textos añadidos como prefijos y sufijos entre comillas...igualmente existen dos formatos genéricos: 'General' o 'Estándar' (según versión de Office).
Por supuesto es imposible cubrir el 100% de formatos personalizados aplicables, pero al menos discriminaremos los máximos posibles: quitar colores de fuente y textos entrecomillados.

Para eliminar estas 'subcadenas de texto' del formato de celda obtenido con INDICAR.CELDA usaremos las función SUSTITUIR aplicada sobre la división de ésta en partes (lo que lograremos con XMLFILTRO) Con todos estos antecedentes insertaremos nuestra fórmula, apoyándonos en LET para facilitar su comprensión y construcción. En la celda E2 añadimos:
=LET(formato;FormatoCelda;
EliminoCaracteresNoDeseados;SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(formato;"""";"[");">";"&gt;");"<";"&lt;");"]";"[");
formatoLimpio;UNIRCADENAS("";1;SI.ERROR(INDICE(SI.ERROR(XMLFILTRO("<i><f><d>"&SUSTITUIR(EliminoCaracteresNoDeseados;"[";"</d><d>")&"</d></f></i>";"//f/d");"");SECUENCIA(LARGO(formato);1;1;2));""));
TipoFecha;SUMA(SI.ERROR(ENCONTRAR({"d"\"m"\"a"};formatoLimpio);0));
SI(O(formatoLimpio="Estándar";formatoLimpio="General");"No fecha";SI(TipoFecha>0;"Es fecha";"No fecha")))

En la primera línea de nuestra fórmula obtenemos el formato de la celda a estudio, llamando a la macrofunción generada previamente 'FormatoCelda':
=LET(formato;FormatoCelda;

En la siguiente sustituimos caracteres ASCII que pueden dar problemas a la función XMLFILTRO, tales como: < > "comillas" o el & Los sustiutiremos por sus equivalentes en HTML:
> por &gt; o &#62;
< por &lt; o &#60;
& por &amp; o &#38;
o
" por &quot; o &#34;
entre otros...
De ahí la siguiente línea en nuestra fórmula:
EliminoCaracteresNoDeseados;SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(formato;"""";"[");">";"&gt;");"<";"&lt;");"]";"[");
donde sustituimos caracteres especiales, y finalmente el cierre de corchete lo cambiamos por 'apertura corchete' .... de tal forma que solo tengamos ese caracter [ para determinar las partes/subcadenas a 'eliminar.

El paso siguiente divide con XMLFILTRO en secciones nuestro formato:
formatoLimpio;UNIRCADENAS("";1;SI.ERROR(INDICE(SI.ERROR(XMLFILTRO("<i><f><d>"&SUSTITUIR(EliminoCaracteresNoDeseados;"[";"</d><d>")&"</d></f></i>";"//f/d");"");SECUENCIA(LARGO(formato);1;1;2));""));
UNIRCADENAS se encarga de volver a unir solo las partes que nos interesan, dejando fuera/eliminando de la nueva composición, aquello que inicialmente estuviera entre corchetes [...] o entre comillas "...".
Lo que conseguimos recuperando las filas indicadas con el vector:
SECUENCIA(LARGO(formato);1;1;2)
Serían, curiosamente solo las filas impares!!

Por tanto nos quedamos exclusivamente con la 'parte pura del formato'

En el paso siguiente buscamos los caracteres d, m y a típicos de las fechas:
TipoFecha;SUMA(SI.ERROR(ENCONTRAR({"d"\"m"\"a"};formatoLimpio);0));
si encuentra alguno de esos caracteres sumará, y por tanto deberemos suponer que el formato corresponde a una fecha.

Finalmente, con todos los cálculos aplicados, nuestra fórmula termina con un condicional que discrimina dos formatos: General o Estándar, que por contener alguno de esos caracteres: m d a nos confundirían:
SI(O(formatoLimpio="Estándar";formatoLimpio="General");"No fecha";SI(TipoFecha>0;"Es fecha";"No fecha")))
Si nos fijamos en los formatos mas habituales, nuestra fórmula funciona perfectamente... e incluso para fechas con formatos personalizados:

Es curioso el último formato del ejemplo:
[Azul][<10]0,0;[Verde][>200]d/m/aa;[Amarillo]"dma";@*.
donde la función de VBA ISDATE no lo identifica como fecha, y como nuestra fórmula, al encontrar esa parte d/m/aa de la segunda sección del formato personalizado, sí retorna un verdadero, reconociéndola como fecha...

Insisto que no cumple el 100% de los casos, pero está muy cerca ;-)

Seguir leyendo...

Power Query: Table.Buffer trabajando en memoria

Una breve entrada para hablar sobre la función M: Table.Buffer(table as table) as table
La verdad es que es bastante controvertida esta función (o su equivalente para Listas: List.Buffer(list as list) as list).
A priori la idea planteada es buena... carga o almacena en temporal (concretamaente en el búfer) una tabla o lista, y la aisla de los cambios externos durante la evaluación.

El búfer - término informático difícil de comprender, que no es ni la caché ni la ram.. pero se parece- es la memoria de transferencia, y al igual que la memoria caché también es una memoria almacenamiento temporal pero que tiene una finalidad distinta.
El fin último del 'búfer' es asegurar que los datos que se transfieren sea estables y constantes.

Esto es, suponemos que almacenar en el búfer una de nuestras tablas/listas durante la operativa de creación de nuestras consultas es buena idea, ya que agiliza y asegura el proceso de 'cálculo'.
Obviamente esto tiene un consumo de memooria asociado!!.
A grandes rasgos, sin almacenamiento en el búfer, todas las filas de una tabla se transmitirían a la hoja de cálculo y solo se usaría una pequeña cantidad de memoria.
Pero con el almacenamiento en el búfer, se leerían todas las filas de nuestra tabla en la memoria.

IMPORTANTE: Power Query limita la cantidad de datos a almacenar en la memoria caché.
Cualquier consumo superior a ese límite se almacenaría en nuestro disco duro, lo cual es muchísimo más lento que acceder a la RAM (incluso si trabajamos con discos duros SSD)!.
En nuestro editor de Power Query podemos controlar la cantidad de RAM que destinamos a este uso.
Accederíamos desde el menú Archivo a las Opciones y Configuración > Opciones de consulta > sección Global > Carga de datos > Opciones de administración de caché

Creo que nadie en su sano juicio adelantaría una opinión definitiva y general de usar o no usar siempre estas funciones, ya que en último término dependerá del volumen de datos que movamos en nuestras consultas y de nuestro equipo (RAM, HD, etc..).
Seguramente bajo ciertas condiciones incluso resulte contraproducente su uso, y consigamos el efecto contrario al deseado... haciendo más lentas nuestras consultas.
La mejor recomendación: prueba a ver que pasa :D

Dicho todo esto, hoy veremos un comportamiento que cuanto menos parece curioso (para mi incluso inexplicable) de la función Table.Buffer
Supongamos una simple tabla en Excel que cargamos al Editor de Power Query y vemos qué tenemos:

Nos vamos a fijar a partir de ahora en la columna 'Orden'.
Añadiremos un nuevo paso a la consulta donde ordenaremos primero por 'País' y luego por 'Producto':

Ha ocurrido algo 'raro' al aplicar esta regla doble de ordenación sobre una tabla ya ordenada en origen... se ha producido un cambio de posición en algunas de las filas (por qué??).
Pero lo más curioso viene ahora.. si aplicamos la función Table.Buffer al paso anterior se produce una ordenación 'extra' en base al campo 'Orden' en sentido descendente ¿¿??

No me quiero quejar, muy práctico en casos como el expuesto en el post anterior (ver aquí), pero desesperante no comprender el motivo de este proceso :'(
Parece (es una suposición) que al tener una orden Table.Sort sobre algunos campos, entiende que tiene sentido forzar una ordenación en sentido inverso. Quiero decir, si con Table.Sort indico un Order.Ascending, Table.Buffer 'aplica' un orden descendente sobre ese campo...
Y si Table.Sort tiene un Order.Descending entonces Table.Buffer aplica un orden ascendente:

Por cierto, con ordenaciones múltiples 'mezcladas Descending + Ascendig', el comportamiento es totalmente errático...

Al margen de todo esto, nos quedaremos con la oportunidad que nos ofrece de agilizar (o no) nuestras consultas...

Seguir leyendo...

Nuevos tipos de datos Enriquecidos Wolfram

Hace tiempo que Microsoft dedica grandes esfuerzos en incorporar nuevas tecnologías dentro de nuestra hoja de cálculo.. hace un par de años lanzó el concepto de Tipo de dato enriquecido.
Comenzó aportando información sobre Cotizaciones y sobre aspectos Geográficos (ver post al respecto).
Más recientemente se lanzó la función HISTORIALCOTIZACIONES (ver aquí).

Estas 'novedades' solo están disponibles para cuentas de Microsoft 365 o bien aquellos que tengan una cuenta de Microsoft gratuita.
No olvides que debes agregar el idioma de edición: inglés, francés, alemán, italiano, español o portugués a las preferencias de idioma de Office!!!... solo así se activarán estos tipos de datos enriquecidos.

La información que muestran estos datos son proporcionados por socios terceros de Microsoft... cosa importante a tener en cuenta ;-)

Después de este repaso a la historia más reciente, tenemos nuevas noticias 👏👏👏

Micorsoft ha lanzado a finales de abril de 2021, para distintos tipos de usuarios de Microsoft 365, una nueva bateria de tipos de datos enriquecidos, gestionado por Wolfram (ver).
Tipos de datos como:
-Química
-Comida
-Yoga
-Ubicaciones
-Personas
-etc

Si bien, debemos tener presente que el reconocimiento de los datos por este sistema únicamente se hace en inglés!!!. Es decir, si quieres información del 'corazón', deberás escribir 'heart' en tu celda... y por supuesto la información devuelta estará igualmente en inglés (no se puede tener todo).

Por lo demás, su funcionamiento es igual al que tenían los clásicos de Cotizaciones o Información geográfica.
Dispondremos en cualquier celda, o como parte de una tabla, una serie de palabras clave respesto al tema que nos interese...
Pongamos que listamos las partes del 'sistema respiratorio':
nose;mouth;pharynx;larynx;trachea;set of bronchi;set of bronchioles;lung
Creamos una tabla con ese listado, y seleccionamos ese campo y accedemos al tipo de datos Wolfram categorizado como 'Anatomía'

La herramienta detectará, muy probablemente, esas partes anatómicas de manera automática... disponiendo en las celdas identificadas un icono de un 'corazón', esto nos dirá que Wolfram reconoce el dato...

Ese icono (que cambia según la categoría elegida...) tiene la funcionalidad añadida de mostrar una etiqueta con toda la información que Wolfram proporciona al respecto:

Sin duda un buen compendio de sabiduría concentrada... ;-)

También podría mostrar un signo de interrogación, indicando que no tiene claro qué es el dato de la celda (bien por un tema de idioma, o por ortografía, o por que simplemente no tiene información al respecto...)

Si presionamos ese icono con la interrogación se abrirá un panel (Selector de datos) a la derecha donde podremos redirigir y ayudar a Wolfram a identificar de eso que estamos hablando...

Algo interesante que he notado es que, a pesar de identificar solo conceptos en inglés, en algunos casos, si escribimos algo en español... Wolfram lo traduce inmediatamente al inglés y proporciona la información solicitada... ¿¿??
No está claro el por qué... y el motivo de por qué solo en algunos casos...
En el ejemplo del sistema respiratorio puedes probar a escribir 'pulmones' y comprobarás que se traduce a 'lung' y muestra los detalles solicitados... ;-)

Otro aspecto interesante de los datos Wolfram es que en muchas categorías contiene información gráfica (mapas, croquis, dibujos, portados, retratos, etc.), aportando un punto visual muy interesante.
En el ejemplo mostrado previamente del sistema respiratorio existe un campo llamado 'Body location' que muestra un esquema del cuerpo humano y dónde se ubica el órgano en cuestión :O

Recuerda también que estos datos enriquecidos, además de proporcionar información a partir de la opción de Agregar columna

también podemos emplear la función FIELDVALUE, la cual al referenciarse a una celda 'enriquecida', identifica el tipo de datos que representa, y ofrece como segundo argumento el listado de campos/información correspondiente:

Igualmente sigue funcionando la referencia directa a la celda seguida del punto y nombre del campo

Estas formas de recuperar información de celdas 'enriquecidas' no son nuevas, son las mismas que ya teníamos para los primeros tipos de datos enriquecidos...

Si necesitas refrescar/actualizar tus datos (fórmulas y/o referencias) puedes presionar Atl+F5 o Ctrl+Alt+F5 (i.e., presionar el botón de Actualizar todo).
OJO!! Atl+F5 (Actualizar) solo refrescará las celdas de nuestro libro que tenga en mismo tipo de datos de nuestra celda activa!!.
Mientras qeu Ctr+Alt+F5 (Actualizar todo) refrescará cualquier celda con cualquier tipo de dato... pero también (es el pero) todas nuestras conexiones del libro (consultas de power query, power pivot, tablas dinámicas, etc...).

Para asegurarte de que actualizas solo lo que quieres es mejor hagas clic derecho sobre la celda con el tipo de datos a actualizar, y desde el menú contextual, acceder a Tipo de datos > Actualizar:

Esto actualizará todas las celdas del libro de trabajo con igual categoría de Tipo enriquecido de datos al de la celda 'clicada', sin el 'peligro' de actualizar conexiones...

Este clic derecho sobre la celda enriquecida y entrar al menú de tipo de datos ofrece dos características interesantes:
-Convertir a texto: es decir, eliminar la funcionalidad añadida que aporta el tipo de datos enriquecido.
-Cambiar: que habilita el cambio de categoría a otra enriquecida...

Este sería un repaso general a las nuevas categorías de tipos de datos enriquecidos que oferta Wolfram...
En próximas entregas mostraré un ejemplo de aprovechamiento de este tipo de datos ;-)

Seguir leyendo...

Power Query: Obtener último valor condicionado

Hoy veremos diferentes alternativas con Power Query para recuperar los últimos valores registrados, de acuerdo a distintas condiciones.
A partir de nuestra tabla de datos, deseamos recuperar aquellos registros únicos que respondan a un criterio múltiple (País y Producto), pero que hayan sido los últimos en registrarse según un campo de orden, fecha, etc...

En concreto veremos tres técnicas que, con algunas diferencias, dan respuesta a nuestro problema.
En la imagen anterior, he señalado en color rojo los registros esperados, que responden a los últimos registros ocurridos para esa dupla: país+producto.

Cargamos la tabla al editor de Power Query.
Y duplicamos en dos ocasiones la consulta creada (hasta tener tres 'queries' iguales.

Las renombraremos como:
-TblORDEN_Opcion1
-TblORDEN_Opcion2
-TblORDEN_Opcion3

En la primera de ellas 'TblORDEN_Opcion1' usaremos la técnica de Quitar duplicados.
Para esto deberemos ordenar adecuadamente nuestros registros...
Por ejemplo ordenando por el campos: 'Orden' (en Descendente!!!, para que los más recientes queden en la parte superior)
Luego Quitaríamos duplicados (en el Editor de Power Query - botón derecho y Quitar duplicados sobre los campos: País & Producto)
La secuencia grabada sería:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblORDEN"]}[Content],
    Tipo_Cambiado = Table.TransformColumnTypes(Origen,{{"Orden", Int64.Type}, {"País", type text}, {"Producto", type text}, {"Precio", type number}}),
    
    Filas_Ordenadas = Table.Buffer(Table.Sort(Tipo_Cambiado,{{"Orden", Order.Descending}})),
    Duplicados_Quitados = Table.Distinct(Filas_Ordenadas, {"País", "Producto"}),
    Filas_Ordenadas1 = Table.Sort(Duplicados_Quitados,{{"País", Order.Ascending}, {"Producto", Order.Ascending}})
in
    Filas_Ordenadas1

O algo más breve:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblORDEN"]}[Content],
    Tipo_Cambiado = Table.TransformColumnTypes(Origen,{{"Orden", Int64.Type}, {"País", type text}, {"Producto", type text}, {"Precio", type number}}),
    
    Filas_Ordenadas = Table.Buffer(Table.Sort(Tipo_Cambiado,{{"País", Order.Ascending}, {"Producto", Order.Ascending}})),
    Duplicados_Quitados = Table.Distinct(Filas_Ordenadas, {"País", "Producto"})
in
    Duplicados_Quitados

En ambos casos el resultado es el correcto:

Fundamental en este caso el empleo de Table.Buffer.
En una entrada posterior explicaremos el por qué.

Otra posible solución la damos en la Opción 2.
Donde los pasos dados serían:
1- Agrupar por 'País' y 'Producto'
2- Modificar algo el código obtenido en Table.Group para conseguir ver el valor máximo del campo Orden
3- Acabar filtrando según las coincidencias obtenidas.

El código completo:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblORDEN"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Orden", Int64.Type}, {"País", type text}, {"Producto", type text}, {"Precio", type number}}),

    //agrupamos por País y Producto
    //Y definimos que el cálculo a ver sea el dato mayor del campo Orden (List.Max([Orden])
    FilasAgrupadas = Table.Group(TipoCambiado, {"País", "Producto"}, {{"Último", each List.Max([Orden]), type number}, {"ListPrecios", each _, type table [Orden=nullable number, País=nullable text, Producto=nullable text, Precio=nullable number]}}, GroupKind.Local),

	//expandimos la agrupación, y recuperamos de nuevo los campos Orden y Precio
    SeExpandió_ListPrecios = Table.ExpandTableColumn(FilasAgrupadas, "ListPrecios", {"Orden","Precio"}, {"Orden","Precio"}),
    
    //para finalmente, en el conjunto de todas las combinaciones posibles, 
    //quedarnos con aquellos registros donde coincidan Orden y Ultimo
    Filas_Filtradas = Table.SelectRows(SeExpandió_ListPrecios, each ([Orden] = [Último])),
    //reordenamos las columnas
    Columnas_Reordenadas = Table.ReorderColumns(Filas_Filtradas,{"Orden", "País", "Producto", "Precio", "Último"})
in
    Columnas_Reordenadas

el resultado es el mismo... solo que este método requiere de más tiempo en su ejecución. Especialmente notable con bases de datos grandes...

La opción 3 es una variante de la anterior, pero con un poco de lenguaje M avanzado.
Vemos el código picado:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblORDEN"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Orden", Int64.Type}, {"País", type text}, {"Producto", type text}, {"Precio", type number}}),

    //agrupamos por Pais y Producto
    //sobre los campos resultantes de una tabla filtrada por los registros correspondientes al valor de orden más alto
    //se consigue ordenando por Descendente el campo Orden, y asignandole un Indice 1, y negativos al posible resto de registros
    // filtrando solo con aquellos cuyo Indice =1, esto es, el mayor valor
    FilasAgrupadas = Table.Group(
        TipoCambiado,
        {"País", "Producto"},
        {
            {"Último", each  Table.SelectRows(
                [tbl=Table.AddIndexColumn( Table.Sort(_,{{"Orden", Order.Descending}}), "Index", 1, -2, Int64.Type)][tbl] , 
                            each ([Index] = 1))}
        }
    ),
    
    //expandimos la tabla resultante de un solo registro
    Expande_ListPrecios = Table.ExpandTableColumn(FilasAgrupadas, "Último", {"Orden","Precio"}, {"Orden","Precio"}),

    //y reordenamos las columnas
    ColumnasReordenadas = Table.ReorderColumns(Expande_ListPrecios,{"Orden", "País", "Producto", "Precio"})
in
    ColumnasReordenadas

Tres técnicas interesantes a aplicar según el caso y el volumen de registros...

Seguir leyendo...

DIAS.LAB.INTL Personalizar días laborables.

Nunca llegaré a conocer Excel.... esa es mi impresión a día de hoy.
O no te acostarás sin saber una cosa más.
Hace un par de días escribí un artículo sobre cómo personalizar el cálculo de días laborables entre dos fechas... Una fórmula válida en toda situación (buena como ejercicio), pero totalmente innecesaria :'( Ya que un día después descubrí entre la ayuda de Microsoft, que la función DIAS.LAB.INTL está ya preparada para obtener ese cálculo.
Y como siempre es bueno reconocer nuestras faltas, aquí va la mía.

La función DIAS.LAB.INTL tiene la siguiente sintáxis:
DIAS.LAB.INTL(fecha_inicial; fecha_final; [fin_de_semana]; [vacaciones])
con un tercer argumento muy interesante, con una serie de codificaciones, para indicar qué entendemos como fin de semana (i.e., como días no laborables), al margen del listado de festivos adicionales del cuarto argumento '[vacaciones]'.

Nada nuevo...
Pues resulta que, desconozco si siempre ha sido así o en qué momento se actualizó, existe para el tercer argumento otra posibilidad para indicar cuáles son nuestros días laborables!!.
Según la documentación:
Los valores de cadena de fin de semana tienen siete caracteres de largo y cada carácter de la cadena representa un día de la semana, comenzando por el lunes. 1 representa un día no laborable y 0 representa un día laborable. Solo los caracteres 1 y 0 están permitidos en la cadena. Si usa 1111111 siempre devolverá 0.

Por ejemplo, 0000011 daría como resultado un fin de semana que es sábado y domingo.
Así pues, de acuerdo a una serie de SIETE dígitos (de 0 y 1), uno por cada día de la semana, con la equivalencia LMXJVSD y representando:
1 - día no laborable
0 - día laborable
Podremos personalizar nuestras semanas de trabajo.
Por ejemplo, si mi semana laboral es el lunes y viernes exclusivamente, mi argumento sería: "0111011"
=DIAS.LAB.INTL(B2;C2;"0111011")

Asombroso!... super flexible, con la posibilidad añadida de incorporar festivos adicionales!!

Por aportar mi granito de arena y dar una posibilidad más literal a la hora de componer el código de siete dígitos, montaremos la siguiente fórmula a insertar como tercer argumento:

La fórmula en E4 sería:
=DIAS.LAB.INTL(B2;C2;
UNIRCADENAS("";FALSO;--NO(ESNUMERO(COINCIDIR({"L"\"M"\"X"\"J"\"V"\"S"\"D"};TRANSPONER(XMLFILTRO("<i><f><d>"&SUSTITUIR(D2;"|";"</d><d>")&"</d></f></i>";"//f/d"));0)))))
Conseguimos entonces informar el tercer argumento con los días laborales (celda D2) indicados de forma literal: L|V en lugar de numéricamente: "0111011"
La 'formulita':
--NO(ESNUMERO(COINCIDIR({"L"\"M"\"X"\"J"\"V"\"S"\"D"};TRANSPONER(XMLFILTRO(""&SUSTITUIR(D2;"|";"")&"";"//f/d"));0))))
es la encargada de obtener la secuencia (vector) de 0 y 1 de siete dígitos que buscamos... para luego obtener el código en formato texto que necesitamos empleando UNIRCADENAS.

Si no te gusta usar la 'barra vertical' y prefieres la secuencia directa de días de la semana: LMJ o similar. Puedes usar en lugar de XMLFILTRO la combinación de EXTRAER, LARGO y SECUENCIA.
En la fórmula siguiente, además, he incluido el cuarto argumento de festivos:
=DIAS.LAB.INTL(B2;C2;
UNIRCADENAS("";FALSO;--NO(ESNUMERO(COINCIDIR({"L"\"M"\"X"\"J"\"V"\"S"\"D"};TRANSPONER(EXTRAE(D3;SECUENCIA(1;LARGO(D3));1));0))));
B7:B9)

Reflexión personal: investiga soluciones estándar antes de desarrollar algo nuevo :D

Seguir leyendo...

Contar días laborables indicados entre fechas

Hace unos días, en una formación, una alumna me preguntaba por la existencia de alguna función que calculara los días laborables transcurridos entre dos fechas, pero contando solo los días de la semana que ella indicara de manera personalizada.

Ella necesitaba personalizar los días laborables en cada caso (solo Lunes y Jueves, o solo Lunes, Martes y Viernes, etc). Le desarrollé de manera improvisada una fórmula para salir del paso; pero tras acabar la formación, pensé en hacer esa fórmula más general y extensiva para cualquier caso.
Y ese es el tema de hoy.

Dada una fecha inicial (celda B2) y otra final (celda C2), queremos conocer cuántos días de los elegidos en la celda D2, abreviados con una letra (L,M,X,J,V,S,D) y separados por la barra vertical, indiquemos (ejemplo: L|J, o L|X|S|D, etc).

Emplearé la función LET (a la espera la función LAMDA sea de uso general).
Así mismo emplearé en el desarrollo dos cálculos ya vistos en el blog:
- Separar caracteres (ver aquí)
- Producto de condiciones con criterio lógico O (ver aquí)

Con los antecedentes expuesto, veammos la fórmula buscada en E2:
=LET(FechaInicial;B2;
FechaFinal;C2;
DiasLab;TRANSPONER(XMLFILTRO("<i><f><d>"&SUSTITUIR(D2;"|";"</d><d>")&"</d></f></i>";"//f/d"));
SerieEntreFechas;SECUENCIA(FechaFinal-FechaInicial+1;1;FechaInicial);
DiasSemana;DIASEM(SerieEntreFechas;2);
PosDiasLab;COINCIDIR(DiasLab;{"L"\"M"\"X"\"J"\"V"\"S"\"D"};0);
Coincidencias;DiasSemana=PosDiasLab;
SUMAPRODUCTO(MMULT(--Coincidencias;SECUENCIA(COLUMNAS(Coincidencias);1;1;0)))
)

Podemos ver el paso a paso que realiza la fórmula descomponiendo cada línea...
El primer paso es simple y ya visto:
DiasLab;TRANSPONER(XMLFILTRO("<i><f><d>"&SUSTITUIR(D2;"|";"</d><d>")&"</d></f></i>";"//f/d"));
con esta función dividimos un cadena de texto, como la del ejemplo: L|V, en un vector de n columnas {L\V}.

En el siguiente paso generamos la serie de todas las fechas posibles entre las dos dadas con la función SECUENCIA:
SerieEntreFechas;SECUENCIA(FechaFinal-FechaInicial+1;1;FechaInicial)
obteniendo un vector de dichas fechas...
A continuación calculamos para cada fecha de nuestra serie aplicándole la función DIASEM:
DiasSemana;DIASEM(SerieEntreFechas;2)
esto nos convierte la serie de fechas en un nuevo vector de números del 1 al 7 (con una equivalencia: lunes=1, martes=2,..., domingo=7).
Un paso importante es el siguiente. Aquí obtenemos y transformamos el texto de días buscados en números del 1 al 7. Esto es, si busco L|V, necesito un 1 y un 5.
Esto lo conseguimos con la función COINCIDIR: PosDiasLab;COINCIDIR(DiasLab;{"L"\"M"\"X"\"J"\"V"\"S"\"D"};0)
Ya casi estamos...
Si cruzamos ambos vectores: días buscados con días listados:
Coincidencias;DiasSemana=PosDiasLab
obtendremos una matriz de FALSOS y VERDADEROS de acuerdo a dichas coincidencias...
Solo nos queda sumar y acumular coincidencias. Último paso:
SUMAPRODUCTO(MMULT(--Coincidencias;SECUENCIA(COLUMNAS(Coincidencias);1;1;0)))
Con la técnica descrita aquí, tendremos nuestro resultado final...

Listo. Tenemos una fórmula personalizada para obtener el número de días laborables, personalizados por nosotros, entre dos fechas dadas.

Finalmente, podríamos incorporar el concepto de festivos:
En la celda E3 y teniendo el cuenta el rango de festivos dado en B7:B9.
=LET(FechaInicial;B2;
FechaFinal;C2;
rangoFestivos;B7:B9;
DiasLab;TRANSPONER(XMLFILTRO("<i><f><d>"&SUSTITUIR(D2;"|";"</d><d>")&"</d></f></i>";"//f/d"));
SerieEntreFechas;SECUENCIA(FechaFinal-FechaInicial+1;1;FechaInicial);
DiasSemana;DIASEM(SerieEntreFechas;2);
PosDiasLab;COINCIDIR(DiasLab;{"L"\"M"\"X"\"J"\"V"\"S"\"D"};0);
Coincidencias;DiasSemana=PosDiasLab;
CruceFestivos;-MMULT(SI(SerieEntreFechas=TRANSPONER(rangoFestivos);1;0);SECUENCIA(FILAS(rangoFestivos);1;1;0));
SUMA(--((MMULT(--Coincidencias;SECUENCIA(COLUMNAS(Coincidencias);1;1;0))+CruceFestivos)>0)))

¡¡Anticipo!!. En dos días publicaré una solución estándar de Excel.

Seguir leyendo...