jueves, 29 de julio de 2021

Power Query: Table.ReplaceValue condicionado

Hoy veremos un ejercicio sencillo pero muy práctico con Power Query: Aplicar sobre una columna un reemplazamiento condicionado a valores de terceras columnas.
En el ejemplo que propongo vamos a reemplazar los elementos de la columna 'Responsable' (cualquiera de ellos) que coincida con un país concreto ('ES') y un año en particular ('2020').
Power Query: Table.ReplaceValue condicionado

Así pues cargaremos nuestra tabla desde la ficha Datos > grupo Obtener y transformar > Desde Tabla o rango, y desde el editor de consultas de Power Query, seleccionamos nuestra tabla recien cargada y presionaremos Editor avanzado, donde implementaremos el siguiente código M:
let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    //dejamos el tipo cambiado...
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"Cliente", type text}, {"Producto", type text}, {"País", type text}, {"Responsable", type text}}),

    //usamos la función Table.ReplaceValue
    //Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table
    ReemplazoValorCondicionado=
            Table.ReplaceValue(TipoCambiado,            //table sobre la que trabajamos 
                                each [Responsable],     //valor viejo a cambiar
                                //y con la condición definimos el nuevo valor por el que sustituiremos.. condicionado
                                each if ([País]="ES" and Date.Year([Fecha])=2020 ) then "Ismael" else [Responsable],
                                Replacer.ReplaceValue, //tipo de reemplazamiento
                                {"Responsable"})  //columna donde buscar el valor a cambiar
in
    ReemplazoValorCondicionado

Power Query: Table.ReplaceValue condicionado

Especialmente interesante el uso del 'each' que hemos aplicado, que permite recorrer fila por fila, aplicando, si cruza con las condiciones dadas, el reemplazamiento buscado...

martes, 27 de julio de 2021

Macro Excel 4.0- EVALUAR. Elegir entre fórmulas

Cada día que pasa Microsoft nos ofrece nuevas y potentes herramientas y funciones, pero no debemos olvidar el pasado, ya que éste nos sigue dando opciones tan potentes como las nuevas.
Hoy hablaré de la función de macro Excel 4.0 EVALUAR.
Función muy interesante, y de amplia difusión en cualquier lenguaje de programación (EVALUATE en DAX o en VBA, o Expression.Evaluate en M-Power Query'), que hablita o fuerza entender una expresión de texto como una referencia y devuelve el resultado de ésta (a grandes rasgos).
Esta función EVALUAR(fórmula como texto) dentro del entorno de las funciones macro 4.0 realiza precisamente esta conversión.

Recuerda que este tipo de funciones la tenemos que desarrollar siempre dentro de la herramienta de nombres definidos. NO está operativa directamente en las celdas!!!

Planteemos el siguiente ejemplo.
Tenemos unos cálculos desarrollados que representan unos ratios estándar que resumen cierta información para un país concreto, tomados los datos de una tabla en nuestro libro:
Macro Excel 4.0- EVALUAR. Elegir entre fórmulas

Desarrollamos las fórmulas para nuestros ratios... pero antes crearemos los dos siguientes nombres definidos. Para lo cual (muy importante!) nos situaremos en la celda K3 (donde vamos a crear nuestra fórmula):
pais =!I3. Es decir, una celda a nuestra izquierda.
ratio =!H3. Es decir, dos celdas a nuestra izquierda.
Notemos que hemos añadido el signo de exclamación antes de la celda. Si no hicieramos esto, al aceptar, se referiría siempre a esa celda de la hoja en que se creó el nombre definido!!.
Sin embargo, al disponer la exclamación, estamos creando una referencia relativa que podemos usar sobre cualquier hoja del libro :OOO (supertruco!!)
Podemos crear nuestros 'ratios'. Así en K3 creamos el ratio A con la fórmula:
=SUMAPRODUCTO(TblVTAS[[Unidades]:[Unidades]]*(TblVTAS[[País]:[País]]=pais)*(TblVTAS[[Precio]:[Precio]]))
Nos indicará el Total de las ventas para el país elegido.

En K4 el ratio B:
=CONTAR.SI(TblVTAS[País];pais)
Nos dirá el número de veces que se ha vendido en el país marcado.

En K5 el ratio C:
=PROMEDIO.SI.CONJUNTO(TblVTAS[Unidades];TblVTAS[País];pais)
Calcula la media de unidades vendidas en el país.

En K6 el ratio D:
=CONTARA(UNICOS(FILTRAR(TblVTAS[Cliente];TblVTAS[País]=pais)))
Clientes únicos para ese país.

Y en K7 el último ratio E:
=TEXTO(
INDICE(FECHA(2021;SECUENCIA(12);1);
COINCIDIRX(MAX(SUMAR.SI.CONJUNTO(TblVTAS[Unidades];TblVTAS[Fechas];">="&FECHA(2021;SECUENCIA(12);1);TblVTAS[Fechas];"<="&FIN.MES(FECHA(2021;SECUENCIA(12);1);0);TblVTAS[País];pais));SUMAR.SI.CONJUNTO(TblVTAS[Unidades];TblVTAS[Fechas];">="&FECHA(2021;SECUENCIA(12);1);TblVTAS[Fechas];"<="&FIN.MES(FECHA(2021;SECUENCIA(12);1);0);TblVTAS[País];pais)));
"mmmm")

Donde obtendremos el mes con mayor unidades vendidas en el país.

A continuación crearemos dos nuevos nombres definidos:
Celda =FORMULATEXTO(INDICE(DATOS!$K$3:$K$7;COINCIDIR(ratio;DATOS!$I$3:$I$7;0)))
fxEVALUAR =EVALUAR(Celda)
Con el nombre definido 'Celda' recuperamos el texto de la fórmula que localizamos con la función INDICE (recuerda que una característica especial de INDICE es que retorna el valor de la celda, pero que también se puede interpretar como una referencia!!).
Es decir, con INDICE, en este contexto, identificamos la referencia de la celda correspondiente al ratio buscado!.

Otras funciones, también macros 4.0, que podríamos haber empleado para recuperar el texto de las fórmulas serían:
=INDICAR.FORMULA(celda_con_fórmula)
=INDICAR.CELDA(6;celda_con_fórmula)

Y finalmente, EVALUAR(Celda) convierte el texto obtenido en una fórmula tal cual la hubieramos escrito :O
Así pues, con nuestros ratios cálculados, nombres definidos con funciones macros 4.0 creados, podemos gestionar la elección de cualquiera de los ratios.
Nos podemos ir a otra hoja y crear el siguiente cuadro:
Macro Excel 4.0- EVALUAR. Elegir entre fórmulas

Donde podemos comprobar cómo en el rango resultado en D3:D8 aplicamos siempre la misma fórmula:
=fxEVALUAR
i.e., llamamos al mismo nombre definido, el cual depende del ratio elegido dos celdas a su izquierda, y del país situado una celda a su izquierda.

Tenemos por tanto una elección de fórmulas sujeta al ratio inicialmente formulado, tal como necesitábamos.

jueves, 22 de julio de 2021

Power Query: Cruce con coincidencia parcial

Os propongo hoy un ejercicio interesante donde cruzar o combinar dos tablas de acuerdo a una coincidencia parcial entre campos de dichas tablas.

Power Query: Cruce con Coincidencia Parcial

Se trata en definiva de recuperar la cuenta contable asociada a cada 'Descripción', según los 'Conceptos' definidos.
El handicap está en que las Descripciones y los Conceptos no son iguales, y solo coinciden parcialmente!!.

Conseguiremos esto empleando funciones M como:
Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical
esta función devolverá TRUE o FALSE si detecta que el primer argumento 'text' contiene el segundo argumento 'substring'.
Interesante el argumento opcional 'comparer' que nos da tres opciones:
-Comparer.Ordinal: se usa para realizar una comparación ordinal exacta.
-Comparer.OrdinalIgnoreCase: se usa para realizar una comparación ordinal exacta que no distingue mayúsculas de minúsculas.
-Comparer.FromCulture: para una comparación compatible con referencias culturales.

Otra función que emplearemos será:
Table.SelectRows(table as table, condition as function) as table
la cual nos devuelve una tabla con las filas de la 'table' que coincidan con la selección dada en el argumento 'condition'.

Además haremos uso de la función Table.Buffer, de la que puedes leer algo más aquí.

Comenzaremos cargando solo como conexión nuestras dos tablas...

A continuación accederemos al editor de Power Query creando una consulta en blanco (que llamaremos 'TblCRUCE_CoincidenciaParcial') donde escribiremos la siguiente consulta:
let
//cargamos la tabla con los movimientos contables
Origen = TblMAYOR,
//cargamos en memoria la tabla de cuentas
//ya que vamos a llamarla recurrentemente una vez por cada fila de la tabla MAYOR
Buffer_TblCUENTAS = Table.Buffer(TblCUENTAS),

//añadimos una columna con el dato de 'cuenta contable' que cruce..
CoincidenciaParcial=
        Table.AddColumn(Origen, "Cuenta",
                        //declaramos una variable que nos permita ir fila por fila en la tabla MAYOR
                        (RecorreCadaFiladeMAYOR)=>
                        //y seleccionaremos la fila o filas de la tabla CUENTAS cargada en memoria
                        Table.SelectRows(Buffer_TblCUENTAS,
                                        //cuando después de recorrer cada fila de CUENTAS
                                        (RecorreCadaFilaCUENTA)=>
                                        //encuentre que en la Descripción existe el Concepto
                                        //sin hacer distinciones entre mayúsculas y minúsculas
                                        Text.Contains(RecorreCadaFiladeMAYOR[Descripción],
                                                      RecorreCadaFilaCUENTA[Concepto],
                                                      Comparer.OrdinalIgnoreCase))[Cuenta contable]{0})
in
    CoincidenciaParcial

Power Query: Cruce con Coincidencia Parcial


Date cuenta que Table.SelectRows en realidad nos devuelve una tabla!!, por lo que habitualmente bastaría con expandir las columnas.. pero en este caso es más simple recuperar el campo [Cuenta contable] y de la lista obtenida, al tener a priori bastante claro que solo debería haber una única coincidencia, recuperamos el primer dato de esa lista (usando {0}).

Especialmente interesante en este ejemplo propuesto es el uso de Table.Buffer para agilizar la busqueda una y otra vez sobre la tabla de CUENTAS...
Si no la usaramos para cada fila tendría que cargar una y otra vez todos los datos de CUENTAS, con el consiguiente retardo en la ejecución de nuestra query...

Por otra parte, también interesante, es el uso de variables para concretar los argumentos iterativos de Table.AddColumn y Table.SelectRows.
Como habíamos mencionado previamente Table.SelectRows devuelve una tabla con las filas de la 'table' que coincidan con la selección dada en el argumento 'condition'; por lo que obviamente debe recorrer fila por fila para detectar si dicha fila cumple o no la condición...
y Table.AddColumn incorpora una nueva columna a nuestra tabla. Sabiendo que los valores de la columna se calculan usando la función de selección especificada en la que cada fila se toma como una entrada (i.e., recorre fila por fila para realizar el cálculo y obtener el resultado!!).

Esto podría ser equivalente, hasta cierto punto, con lo expuesto en las entradas del blog sobre funciones recursivas, por ejemplo lo explicado en este artículo.

martes, 20 de julio de 2021

Power Pivot: ADDCOLUMNS vs SELECTCOLUMNS

Hoy toca un poco de DAX para Power Pivot para Excel.
En concreto vamos a comparar el rendimiento de dos funciones similares: ADDCOLUMNS y SELECTCOLUMNS:
La primera obviamente agrega columnas calculadas a una tabla en sí, o también a una expresión de tabla dada:
ADDCOLUMNS(table; name; expression;[; name; expression]…)
Con esta función obtendremos por tanto una nueva tabla con todas sus columnas originales y las nuevas agregadas.


Mientras la segunda devuelve una tabla solo con columnas existentes y seleccionadas de una tabla, o bien con nuevas columnas generadas 'artificialmente' con DAX:
SELECTCOLUMNS(table; name; scalar_expression [; name; scalar_expression]…)
Además debemos saber que esta función nos devuelve una tabla con el mismo número de filas que la tabla original; sabiendo que cada expresión se evalúa en el contexto de una fila a partir de esa tabla original especificada.

A priori, ambas tienen muchos aspectos en común.. y de hecho se emplean indistintamente... aunque en estos puntos comentados reside su pequeña diferencia; ¿el rendimiento de una y otra no pueden ser igual, ya que una trabaja sobre la totalidad de las tablas, mientras que la otra solo sobre las columnas seleccionadas!!??.
Imagina un caso extremo. Tenemos una tabla con 2.000.000 de registros y 1.000 columnas.
¿Cuál piensas que sería el tiempo necesario de procesamiento para realizar un cálculo simple empleando una y otra función??... Uno que necesita trabajar sobre todas esas columnas aunque no se utilicen en el cálculo?, u otro que solo opera sobre 2-3 columnas, que son las estrictamente necesarias??.

Pongamos un ejemplo y trabajemos brevemente sobre este y comprobemos tiempos de ejecución.
Partimos de dos tablas:
1- Tabla de ventas ('TblVENTAS') con 600.000 registros y 15 columnas.
2- Tabla de precios ('TblPRECIOS') con 9 registros y 2 columnas.

Power Pivot: ADDCOLUMNS vs SELECTCOLUMNS
Cargamos ambas al modelo de datos y las relacionamos por el campo Artículo-Producto:
Power Pivot: ADDCOLUMNS vs SELECTCOLUMNS

Crearemos ahora dos medidas que acumulen para cada cliente solo los tres importes más altos.
Así en el área de cálculo de la TblVENTAS incorporamos:
fxSELECTCOLUMNS:=VAR DosCols=SELECTCOLUMNS(TblVENTAS;
		"Customer";TblVENTAS[Cliente];
		"Total Sales €"; CALCULATE(SUMX(TblVENTAS;TblVENTAS[Unidades]*RELATED(TblPRECIOS[Precio Unitario])))) 
RETURN
SUMX(TOPN(3; DosCols;[Total Sales €];DESC);[Total Sales €])


fxADDCOLUMNS:=VAR N_Cols=ADDCOLUMNS(TblVENTAS;
			"Total Sales €"; CALCULATE(SUMX(TblVENTAS;TblVENTAS[Unidades]*RELATED(TblPRECIOS[Precio Unitario])))) 
RETURN
SUMX(TOPN(3; N_Cols;[Total Sales €];DESC);[Total Sales €])


La primera medida 'fxSELECTCOLUMNS':
fxSELECTCOLUMNS:=VAR DosCols=SELECTCOLUMNS(TblVENTAS;
		"Customer";TblVENTAS[Cliente];
		"Total Sales €"; CALCULATE(SUMX(TblVENTAS;TblVENTAS[Unidades]*RELATED(TblPRECIOS[Precio Unitario])))) 
RETURN
SUMX(TOPN(3; DosCols;[Total Sales €];DESC);[Total Sales €])

selecciona la columna 'Cliente' renombrándola como 'Customer',
y por otro lado incorpora una nueva columna calculada 'Total Sales €'.

De forma similar la segunda medida 'fxADDCOLUMNS':
fxADDCOLUMNS:=VAR N_Cols=ADDCOLUMNS(TblVENTAS;
			"Total Sales €"; CALCULATE(SUMX(TblVENTAS;TblVENTAS[Unidades]*RELATED(TblPRECIOS[Precio Unitario])))) 
RETURN
SUMX(TOPN(3; N_Cols;[Total Sales €];DESC);[Total Sales €])

añade a la tabla de ventas (con todas sus columnas) una nueva...

Nada especial a priori... Para controlar los tiempos de ejecución de ambas medidas nos iremos a DAX Studio (https://daxstudio.org/) y ejecutaremos ambas medidas, comparando los milisegundos de cada una de ellas.
En otro momento explicaré el cómo... de momento me quedaré con unos pantallazos de resultados:
Power Pivot: ADDCOLUMNS vs SELECTCOLUMNS
Comprobamos que las diferencias son mínimas entre ambas medidas!!!
Podríamos pensar que las diferencias es por que nuestra tabla tiene pocas columnas, pero realizando el mismo ejercicio con 200 columnas, los resultados son equiparables y prácticamente idénticos!! :O

Es decir, en las medidas propuestas parece tener más relevancia el cálculo evaluado que la elección de la función.

Donde aparecen verdaderamente las diferencias entre una y otra función es cuando devolvemos el conjunto de columnas, esto es, si sobre un origen de 200 columnas y 200.000 registros, generamos estas queries:
fxSELECTCOLUMNS:=VAR DosCols=SELECTCOLUMNS(TblVENTAS;
		"Customer";TblVENTAS[Cliente];
		"Total Sales €"; CALCULATE(SUMX(TblVENTAS;TblVENTAS[Unidades]*RELATED(TblPRECIOS[Precio Unitario])))) 
RETURN
DosCols


fxADDCOLUMNS:=VAR N_Cols=ADDCOLUMNS(TblVENTAS;
			"Total Sales €"; CALCULATE(SUMX(TblVENTAS;TblVENTAS[Unidades]*RELATED(TblPRECIOS[Precio Unitario])))) 
RETURN
N_Cols

En ambos casos se crean dos tablas... pero en el caso de SELECTCOLUMNS una tabla de dos columnas... y en el caso de ADDCOLUMNS una tabla de 201 columnas. Nota la diferencia abismal entre tiempos:
Power Pivot: ADDCOLUMNS vs SELECTCOLUMNS

167.730 ms versus 3.563 ms
Increible!!, pero esperado. ADDCOLUMNS queda destrozado frente a SELECTCOLUMNS

Reflexión y conclusión personal: Cuando el origen tenga un número elevado de columnas, siempre mejor trabajar con SELECTCOLUMNS...
cuando haya un número 'pequeño' de columnas, es el proceso de evaluación o cálculos añadidos los que aportan tiempo de proceso... y no hay aparente diferencia entre una y otra.
Asi pues, diría en base a esto... usemos siempre SELECTCOLUMNS, cuando no necesitemos el resto de columnas ;-)

jueves, 15 de julio de 2021

Power Query: Anexar tablas a partir de una lista

Hoy veremos una forma sencilla de anexar tablas, a nuestra elección, desde una lista dada:
Power Query: Anexar tablas a partir de una lista


Optaremos por dos métodos:
List.Transform o List.Generate
pero en ambos casos necesitaremos emplear las funciones descritas en esto otro post del blog:
Expression.Evaluate y Expression.Identifier

Comenzaremos nuestro ejercicio cargando todas las tablas fuente: 'ene', 'feb', 'mar', 'abr', 'may' y 'jun'
y de igual forma la Tabla 'Meses_Anexar' que contendrá, ad hoc, el listado de meses que nos interesa recuperar...

Cargaremos todas ellas en modo solo conexión.
Ya desde el editor de Power Query crearemos una consulta en blanco donde insertaremos el siguiente código:
let 
// Table.ToList(table as table, optional combiner as nullable function) as list
//convertimos en lista nuestra tabla, ya que lo necesitamos en ese tipo de datos para la siguiente transformación
Meses=Table.ToList(Meses_Anexar),

// List.Transform(list as list, transform as function) as list
//evaluamos y transformamos cada elemento de la lista (cada tabla)
// Expression.Evaluate se encarga de entender los textos de la lista como lo que son: Tablas
Lista_Tablas=List.Transform(Meses, each Expression.Evaluate( Expression.Identifier(_),#shared)),

// Table.Combine(tables as list, optional columns as any) as table
// Combinamos las ya transformadas Tablas
resultado=Table.Combine(Lista_Tablas)

in 
    resultado


Como vemos un método muy simple y efectivo...

Una alternativa (de entre muchas) sería emplear List.Generate (ver aquí).
De igual forma que antes, con todas las tablas ya cargadas, crearemos una nueva consulta en blanco donde escribiremos:
let 
// Table.ToList(table as table, optional combiner as nullable function) as list
// pasamos a tipo Lista nuestra tabla de meses a anexar
Meses=Table.ToList(Meses_Anexar),

// List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list
// generamos una Lista de Tablas...
Lista_Tablas = List.Generate(
    () => [i=-1, tabla=#table({"Mes","Importe"},{})],      // punto de partida: Dos variables definidas en un Registro
    each [i] < List.Count(Meses),           // condición - control de salida!!!
    each [i=[i]+1, 
          tabla=Expression.Evaluate( Expression.Identifier(Meses{i}),#shared)],     // siguiente valor. Nuevo Registro
    each [tabla]                            // el Selector determina que valor a devolver
                    ),
                    
// Table.Combine(tables as list, optional columns as any) as table
//componemos una Tabla a partir de los Registros generados con List.Generate
resultado=Table.Combine(Lista_Tablas)

in 
    resultado

Un buen ejercicio de repaso de esta interesante función...

Con cualquiera de los dos métodos expuestos conseguimos nuestro objetivo: dinamizar el anexado de diferentes tablas a partir de un lista personalizable.

martes, 13 de julio de 2021

Power Query: Personaliza tus funciones con Metadatos

La semana pasada publiqué un artículo donde explicaba que eran los Metadatos (ver aquí) y para qué servían...
Hoy le daremos un uso peculiar. Un uso que nos permitirá personalizar los textos de ayuda de una función creada por nosotros en nuestro editor de Power Query.

En primer lugar accederemos al editor de Power Query y crearemos una Consulta en blanco, donde insertaremos el siguiente código de una sencilla función (esto no es lo que nos importa):
(texto1 as text, texto2 as text) as text => 
let 
    txtCompleto=Text.Combine({texto1, texto2}, " ")
in 
    txtCompleto

Power Query: Personaliza tus funciones con Metadatos

Una vez finalizada la función llamada 'fxFuncionConMetadatos' si la seleccionamos veremos que solo nos despliega los parámetros definidos... ninguna información adicional. Muy simple :'(
Power Query: Personaliza tus funciones con Metadatos

Puedes entrar en cualquier otra función estándar de Power Query para comprobar la diferencia... por ejemplo, si accedes a la función Table.ColumnCount veríamos:
Power Query: Personaliza tus funciones con Metadatos


Incorporar toda esa información complementaria a nuestra función será el ejercicio de hoy.

Para ello disponemos de ciertos campos tipo Documentation:
- Documentation.Examples: de tipo list. Corresponde a un lista de objetos de registro con el uso de ejemplo de la función.
Solo se muestra como parte de la información de la función.
Cada registro debe contener los siguientes campos de texto opcionales: Description , Code y Result

- Documentation.LongDescription: de tipo text. Descripción completa de lo que hace la función, que se muestra en la información de la función.
Existe también Documentation.Description para descripciones más breves... LongDescription prioriza sobre .Description, es decir, si definimos la primera, NO aparecerá la sencilla y corta.

- Documentation.Name: de tipo text. Texto que se va a mostrar en la parte superior del cuadro de diálogo de invocación de función.


Otros campos asociados a la documentación de nuestras funciones, en concreto a los parámetros de nuestra función, opcionales, serían:
- Documentation.AllowedValues: de tipo list. Lista de valores válidos para este parámetro. Al proporcionar este campo, se cambiará la entrada de un cuadro de texto a una lista desplegable. Ten en cuenta que esto no impide que un usuario edite manualmente la consulta para proporcionar valores alternativos.

- Documentation.FieldCaption: de tipo text. Nombre descriptivo para mostrar que se va a usar para el parámetro.

- Documentation.FieldDescription: de tipo text. Descripción que se va a mostrar junto al nombre para mostrar.

- Documentation.SampleValues: de tipo list. Lista de valores de ejemplo que se van a mostrar (como texto atenuado) dentro del cuadro de texto.

- Formatting.IsMultiLine: de tipo boolean. Permite crear una entrada de varias líneas, por ejemplo, para pegar en consultas nativas.

- Formatting.IsCode: de tipo boolean. Da formato al campo de entrada para el código, normalmente con entradas de varias líneas.


Otras funciones muy importantes que nos permitirán recuperar los anteriores registros comentados (.Name, .LongDescription, .Examples) son:
- Value.Type(value as any) as type: función M que nos devuelve el 'tipo' del valor indicado.

- Value.ReplaceType(value as any, type as type) as any: función M que habilita el cambio de tipo de una dato.

- Value.Metadata(value as any) as any : función M vista en una entrada previa del blog, que nos devuelve un registro que contiene los metadatos del valor dado.

- Value.ReplaceMetadata(value as any, metaValue as any) as any : función M vista en una entrada previa del blog, que nos permite cambiar la información cargada como metadatos.

- Type.FunctionParameters(type as type) as record: interesante función M que retorna un registro con los valores de campo establecidos en el nombre de los parámetros de type y sus valores establecidos en sus tipos correspondientes.

- Type.FunctionRequiredParameters(type as type) as number : Devuelve un número que indica el mínimo de parámetros necesarios para invocar la entrada type definido.

Un poco raras las dos últimas funciones, pero fáciles de comprender cuando las usemos en el contexto adecuado a continuación...

Diría que estamos listos para generar nuestros metadatos e incorporarlos a la documentación de nuestra función.
A partir de nuestra función personalizada tendríamos:
let 
txtCompleto = (texto1 as text, texto2 as text) as text => 

	Text.Combine({texto1, texto2}, " "),

	//asignamos propiedades a visualizar en los parámetros de nuestra función
	ParametrosTexto = type text meta 
		[Documentation.FieldCaption = "Añade solo texto",
		Documentation.FieldDescription = "Texto",
		Documentation.SampleValues = {"Excelforo"}],
	
	//redefinimos los tipos de la función con las características anteriores
	TipoMiFuncion = type function(
					texto1 as ParametrosTexto,
					texto2 as ParametrosTexto) as text,

	//declaramos los metadata asociados a la documentación de nuestra función
	Documentacion_de_Metadatos= [
		Documentation.Name ="fxFuncionConMetadatos",     //debe ser el nombre de la función (para no confundir...)
		Documentation.LongDescription = "Nuestra función concatena cadenas de texto... y bla bla bla",
		Documentation.Examples = { 
				[Description = "La función nos devuelve una cadena de texto a partir de dos elementos.",
				Code = "fxFuncionConMetadatos(Power, Query)",
				Result="Power Query"]
					} ]
in
	Value.ReplaceType(txtCompleto,Value.ReplaceMetadata(TipoMiFuncion, Documentacion_de_Metadatos))

Y veríamos lo siguiente al seleccionar nuestra función:
Power Query: Personaliza tus funciones con Metadatos


Con lo que ya dispondremos de una completa ayuda para nuestra función ;-)

jueves, 8 de julio de 2021

Tabla Dinámica: Texto en el Área de Valores

Veremos como incorporar textos al área de valores de una tabla dinámica empleando una medida del Modelo de datos de Power Pivot.
Power Pivot-DAX Tabla Dinámica: Texto en el Área de Valores

La idea es que la tabla dinámica cruce la información de 'Status' (de tipo texto) según el País y Año desplegados en las áreas de columnas y filas de la tabla dinámica...
Además en los Totales generales aplicaremos algo de formulación para que muestre el último Status...

Lo interesante del asunto: conseguir que se muestren los textos del campo 'Status' en el área de valores.
Para ello emplearemos la función
CONCATENATEX(table; expression; [delimiter]; [order_by_expression1]; [ASC/DESC]; [order_by_expression2];[ASC/DESC];...) as String
que devuelve una cadena de texto concatenando el resultado de una expresión evaluada para cada fila de nuestra tabla... en definitiva, esa X del final, nos indica el uso iterativo por fila (igual que SUMX, COUNTX, etc...), y CONCATENATE nos dice la acción que realiza ('concatenar').

Igualmente, para obtener en los totales generales de nuestra tabla dinámica el último valor de 'Status', usaremos: LASTNONBLANK(column; expression) as table
que nos devuelve una tabla 'algo especial', de solo una fila y una columna, mostrando el último valor de la columna, filtrado por el contexto actual, donde la expresión no esté en blanco.
Un punto importante de esta función es que ordena automáticamente en ASCENDENTE los valores de la columna indicada, y sobre dicha columna ordenada, recupera el úlitmo dato no vacío.

Para gestionar este cálculo emplearemos la función HASONEVALUE ya vista y explicada aquí.

Ya puestos en antecedentes, el primer paso consistirá en agregar la tabla al modelo de datos de Power Pivot.
Con la tabla cargada añadiremos nuestra medida:
txtStatus:=IF(HASONEVALUE(Tabla1[Años]);
	IF( HASONEVALUE(TABLA1[País]);
			CONCATENATEX(Tabla1;Tabla1[Status];"-";Tabla1[Años];ASC;Tabla1[País];ASC);
			CALCULATE(LASTNONBLANK(Tabla1[Status];TRUE());
                      FILTER(Tabla1; Tabla1[Años]=MAX(Tabla1[Años])  && Tabla1[País]=MAX(Tabla1[País]) )))
   ;CALCULATE(LASTNONBLANK(Tabla1[Status];TRUE());
   			FILTER(Tabla1;Tabla1[Años]=MAX(Tabla1[Años]) && Tabla1[País]=max(Tabla1[País]))))

Power Pivot-DAX Tabla Dinámica: Texto en el Área de Valores


En la siguiente etapa, desde el editor de Power Pivot crearemos una tabla dinámica que llevaremos a nuestra hoja...
LLevaremos el campo 'País' al área de columnas.
El campo 'Año' al área de filas
Y nuestra medida 'txtStatus' al área de valores...
y ahí están !!, los textos aparecen donde solo veíamos números!!.

Y extra tip, en los Totales generales hemos dispuesto los últimos datos no vacíos que aparezcan :OO

Una explicación rápida de la medida sería con un doble IF controlamos la aparición de un único valor respecto del 'Año' primero, y del 'País' después.
Con CONCATENATEX hemos generado la cadena de texto que requeríamos (muy simple), solo necesitábamos recuperar el valor de 'Status', pero nos hemos aprovechado de los criterios de ordenación que ofrece la función.
Para obtener el último valor, basado en una ordenación virtual por los campos Años y País, usamos la estructura formulada:
CALCULATE(LASTNONBLANK(Tabla1[Status];TRUE());FILTER(Tabla1; Tabla1[Años]=MAX(Tabla1[Años])&&Tabla1[País]=MAX(Tabla1[País]) )
donde FILTER nos ayuda a recuperar el registro coincidente con el último dato de Año y País.

Curioso el proceder de la función MAX que devuelve el valor mayor de una columna, y si hablamos de columnas de tipo texto el último valor de acuerdo a un orden alfabético. Los valores lógicos dentro de la columna se ignoran.
Power Pivot-DAX Tabla Dinámica: Texto en el Área de Valores

Viendo el detalle de la tabla dinámica resultante, verificamos lo correcto del proceso ;-)

martes, 6 de julio de 2021

Power Query: Metadata, información adicional para nuestros datos

Sin lugar a duda este tema de hoy: los Metadata es uno de los más curiosos (e interesantes) que nos podemos encontrar...
Todos tenemos claro el concepto de dato, es la información que vemos reflejada en nuestras fuentes, nuestras tablas.. es algo 'palpable', un valor concreto.

¿Pero sabías que puedes añadir información adicional asociado a esos valores?... esto son los Metadata: un Registro de valores que se conoce como registro de metadatos de un valor.
Este registro de metadatos se puede asociar a cualquier tipo de valor; y el resultado de este tipo de asociación es un nuevo valor con los metadatos especificados.

Por tanto, no nos perdamos... un registro de metadatos sigue siendo un registro normal, y podrá estar compuesto de todos los campos y valores de un registro normal, pero al mismo tiempo contener información extra.

Ten presente, para tu tranquilidad, que la asociación de un registro de metadatos con un valor es "no intrusiva", es decir, no cambia el comportamiento del valor principal en las evaluaciones, excepto obviamente en el de las acciones que inspeccionan dichos registros de metadatos de forma directa y explícita.

Aunque no seamos conscientes, todos los valores tiene un registro de metadatos predeterminado, incluso aunque no se haya asignado ninguno, en estos casos el registro de metadatos por defecto es el vacío (i.e., [] ).

Tres funciones M para trabajar sobre el registro de metadatos son:
- Value.Metadata(value as any) as any
La cual nos devuelve un registro que contiene los metadatos de entrada.
- Value.RemoveMetadata(value as any, optional metaValue as any) as any
La cual elimina el registro de metadatos.
- Value.ReplaceMetadata(value as any, metaValue as any) as any
Que sustituye la información recogida en el registro de metados del valor.

Igualmente importante el operador meta para asociar un registro de metadatos a un valor.

Veamos todo esto con algún ejemplo sencillo...
Desde nuestro editor de consultas de Power Query crearemos una consulta en blanco (que llamaré 'Metadata_Ejem1') y accederemos a ella desde el Editor avanzado, donde escribiremo:
let
    md1 = "excelforo" meta [conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],
	//para recuperar 'el campo 'conocimientos'... que la hemos creado como una lista
    dato=Value.Metadata(md1)[conocimientos]
in
    dato

El resultado de la consulta será la lista:
Power Query: Metadata, información adicional para nuestros datos

Por otra parte, si hubieramos querido recuperar el valor principal, podríamos haber hecho:
let
    md1 = "excelforo" meta [conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],
	//para recuperar 'el campo 'conocimientos'... que la hemos creado como una lista
    dato=Value.Metadata(md1)[conocimientos]
in
    Text.Upper(md1)

que nos hubiera devuelto el valor en mayúsculas: EXCELFORO

Veamos otro ejemplo...
Definimos un metadato con una dupla de valores: posición y conocimientos
let
    md1 = "excelforo" meta [posicion=2 , conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],

    dato=Value.Metadata(md1)[conocimientos]{Value.Metadata(md1)[posicion]}
in
    dato

Lo que nos devolverá, sobre la lista de conocimientos, el dato en la posición 2 (en base 0), es decir, 'Power Pivot'.

Otro ejemplo del uso de metadatos para reemplazarlos.
En este caso reemplazaremos el valor asignado al valor 'posición' del registro de metadatos:
let
    md1 = "excelforo" meta [posicion=2 , conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],

    //reemplazamos metadato
    //provocaría fallo al NO mencionar el campo 'conocimientos'
    //ReemplazaMD1= Value.ReplaceMetadata(md1, [posicion=3]),
    
    //recuperamos el valor del registro de metadato de 'conocimientos'
	//  ReemplazaMD1= Value.ReplaceMetadata(md1, [posicion=3, conocimientos=Value.Metadata(md1)[conocimientos]]),
    
    //o también usando el operador de unión/concatenación &
	//	ReemplazaMD1= Value.ReplaceMetadata(md1, [posicion=3] & [conocimientos=Value.Metadata(md1)[conocimientos]]),

	//o mucho más simple forzando una sobreescritura
    ReemplazaMD1=md1 meta [posicion=3],
    
    dato=Value.Metadata(ReemplazaMD1)[conocimientos]{Value.Metadata(ReemplazaMD1)[posicion]}
in
    dato

Al reemplazarlo por 'posición' = 3 la consulta recuperaría el metadato 'VBA para Excel'

LLamando de nuevo al operador meta podemos incorporar nuevos valores al registro de metadatos, por ejemplo, una 'valoración':
let
    md1 = "excelforo" meta [posicion=2 , conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],

    //añade nuevo metadato
    nuevo= md1 meta [valoración=10],

    dato=Value.Metadata(nuevo)[valoración]
in
    dato

Si necesitáramos visualizar el registro de metadatos completo haríamos:
let
    md1 = "excelforo" meta [posicion=2 , conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],

    //añade nuevo metadato
    nuevo= md1 meta [valoración=10]

in
    Value.Metadata(nuevo)

Power Query: Metadata, información adicional para nuestros datos


Y acabamos eliminando metadatos con Value.RemoveMetadata.
Por ejemplo, para eliminar todo el registro de metadatos haríamos:
let
    md1 = "excelforo" meta [posicion=2 , conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],

    //añade nuevo metadato
    nuevo= md1 meta [valoración=10],

    //Eliminamos metadatos
    eliminacion=Value.RemoveMetadata(nuevo)

in
    Value.Metadata(eliminacion)

lo que nos devolvería el vacío, un registro sin valores...

Alternativamente, para eliminar uno de los valores del registro de metadatos podríamos emplear la función ya vista Value.ReplaceMetada. Por ejemplo para quedarnos solo con los valores 'posicon' y 'valoración' haríamos:
let
    md1 = "excelforo" meta [posicion=2 , conocimientos={"Excel","Power Query", "Power Pivot","VBA para Excel"}],
	
    //añade nuevo metadato
    nuevo= md1 meta [valoración=10],
    
    //Eliminamos metadatos
    eliminacion=Value.ReplaceMetadata(nuevo, [posicion=2, valoración=10])

in
    Value.Metadata(eliminacion)

Lo que nos deja un registro de metadatos con esos dos valores...

En la siguiente entrada del blog le daremos un uso especial a estos registros de metadatos ;-)

jueves, 1 de julio de 2021

Power Pivot: RANKX y TOPN

Acabamos con la serie de Top 3 ventas por cliente con Power Pivot y un poco de DAX.
Power Pivot: RANKX y TOPN


Obviamente comenzaremos cargando nuestra TblVENTAS al Modelo de datos de Power Pivot, donde crearemos las dos siguientes medidas:
Sum_Ventas:=SUM([Total])
    //////////
    Top3:=VAR Ordenado=RANKX (
                    ALLEXCEPT(TblVENTAS;TblVENTAS[Cliente]);
	    [Sum_Ventas];
                    [Sum_Ventas];DESC ;Skip           )
                    
RETURN
                  CALCULATE(SUMX(TblVENTAS; IF (Ordenado<=3; TblVENTAS[Total];0));TOPN(3;TblVENTAS;IF (Ordenado<=3;TblVENTAS[Total];0);DESC))

Comprobamos con la segunda medida como generamos una variable 'Ordenado', equivalente al que teníamos en nuestra hoja de cálculo 'orden', que responde de igual forma, esto es, asignando valores 1, 2, 3, etc. para cada cliente.

Si crearamos una medida solo con la función RANKX:
Orden3:=VAR Ordenado=RANKX (
                    ALLEXCEPT(TblVENTAS;TblVENTAS[Cliente]);
	    [Sum_Ventas];
                    [Sum_Ventas];DESC ;Skip           )
                    
RETURN
    Ordenado

comprobaríamos ese aparición ordenada de mayor a menor...
Power Pivot: RANKX y TOPN

Esta función RANKX es una función iterativa, es decir, recorre cada fila asignando el valor ordinal según la posición o clasificación que le corresponda.
RANKX(table, expression[, value[, order[, ties]]])
Especial interés por los dos últimos argumentos: Orden y Ties
Que toma los siguientes parámetros.
Order: 0/FALSE/DESC – descendente; 1/TRUE/ASC – ascendente.
Ties gestiona la clasificación devuelta en caso de 'empate' o valores repetidos. Skip – el orden de los elementos repetidos se salta...; Dense – todos los elementos empatados se cuentan como uno.

De este cálculo nos interesará recuperar aquellos con ranking inferior o igual a 3, para cada cliente.

Esto lo conseguimos con el retorno de la variable 'Ordenado' en el contexto de CALCULATE y SUMX:
 Top3:=VAR Ordenado=RANKX (
                    ALLEXCEPT(TblVENTAS;TblVENTAS[Cliente]);
	    [Sum_Ventas];
                    [Sum_Ventas];DESC ;Skip           )
                    
RETURN
                  CALCULATE(SUMX(TblVENTAS; IF (Ordenado<=3; TblVENTAS[Total];0));TOPN(3;TblVENTAS;IF (Ordenado<=3;TblVENTAS[Total];0);DESC))

donde con CALCULATE podemos aplicar un nivel de filtro de los tres mayores con la función TOPN.
función que nos devuelve, en forma de tabla, las N filas superiores:
TOPN(n_value, table, orderBy_expression, [order[, orderBy_expression, [order]]…])

Así, finalmente podemos recuperar y acumular para cada cliente, exclusivamente aquellos importes del campo 'Total' que tenga nuestra clasificación entre los tres primeros (<= 3)

El resultado, entonces, en nuestra tabla dinámica devuelta a la hoja de cálculo desde el Modelo de datos vemos el resultado...
Power Pivot: RANKX y TOPN


Aprovechando que tenemos la información cargada en el Modelo de datos haremos uso de las funciones CUBO y un poco de MDX para generar un informe similar.
Power Pivot: RANKX y TOPN
Empezamos añadiendo en G4 el conjunto de clientes:
=CONJUNTOCUBO("ThisWorkbookDataModel";"[TblVENTAS].[Cliente].Children";"clientes")
Y a partir de ese conjunto sus miembros en el rango G6:G9:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$G$4;SECUENCIA(RECUENTOCONJUNTOCUBO($G$4)))

Para calcular el total por cliente en H6:H9 añadimos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";G6)
obteniendo el acumulado de la medida Sum_Ventas (definida en los pasos anteriores) para cada cliente

Seguidamente crearemos el conjunto de tres elementos de importes más altos para cada cliente, y en I6:I9 insertaremos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"(Filter(Exists([TblVENTAS].[Orden].Children,{[TblVENTAS].[Cliente].["&$G6&"]}),StrToValue([TblVENTAS].[Orden].CurrentMember.Name)<=3),[Measures].[Sum_Ventas])"; "orden<=3")

donde con distintos cruces entre campos y aplicación de filtros obtenemos el importe de 'Sum_Ventas', i.e., el acumulado para el elemento clasificado como 1,2 o 3 del importe 'Total'.

Con el conjunto creado ya podemos recuperar los tres importes más altos; en K6:K9 para el más alto:
=SI.ERROR(VALORCUBO("ThisWorkbookDataModel";$G6;MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I6;1));0)
en L6:L9 para el segundo más alto:
=SI.ERROR(VALORCUBO("ThisWorkbookDataModel";$G6;MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I6;2));0)
e igual para M6:M9 y el tercero:
=SI.ERROR(VALORCUBO("ThisWorkbookDataModel";$G6;MIEMBRORANGOCUBO("ThisWorkbookDataModel";$I6;3));0)

Solo nos queda sumar estas tres cantidades en J6:J9 y habremos acabado...
Más fácil de aplicar en este caso con un poco de DAX ;-)

En cualquier caso, hemos logrado recuperar los tres importes de ventas más altos por cada cliente, de muy distintas maneras.. abriendo el abaníco de opciones.
Espero te resulte de interés.