Power Query: .ExpandTableColumn vs .Combine

Hay una duda constante a la hora de unir diferentes tablas con Power Query en si es mejor usar Table.Combine o Table.ExpandTableColumn...
Por desgracia, la realidad es que esto no viene nunca solo, y siempre está asociado a otros procesos anexos de transformación que son los que marcan la diferencia de tiempos de ejecución.

Veremos un ejemplo sencillo donde partiendo de tres tablas de 6.000 registros cada una, compararemos ambos métodos y veremos cuál es más ágil.
Nuestras tablas de origen:

Nota que las tres tablas tienen encabezados diferentes, aunque dos campos siempre en común: Productos, Importes

LLegaremos a dos soluciones idénticas entonces, una con la función Table.Combine y una segunda con Table.ExpandTableColumn:

Comencemos con el primer método, donde trabajaremos toda la transformación a nivel de fila, en cada elemento de una columna operada.
En primer lugar accederemos al editor de Power Query directamente, ya que la carga de las tablas la realizaremos 'picando' un poco de código.
Dentro del editor buscaremos la opción de añadir una consulta en blanco dentro de la cual, empleando el editor avanzado, escribiremos para el primer intento, empleando:

let
    // recuperamos cualquier tabla, nombre definido, etc del libro de trabajo
    Origen = Excel.CurrentWorkbook(),
    //nos quedamos exclusivamente con aquellas que comienzen por 'Tbl'
    FiltroTBL = Table.SelectRows(Origen, each Text.StartsWith([Name], "Tbl")),

    //paso duro, donde de una vez seleccionamos solo las columnas 'Producto' e 'Importes'
    // para luego aplicar un filtro múltiple de los producto a y b
    // y finalmente añadimos una nueva columna con el contenido de la columan [Name]
    // todo esto dentro de cada elemento de una columna agregada que contiene una Tabla
    TrabajoTablas = Table.AddColumn(FiltroTBL,"Transforma", 
                        each 
                            let cp=[Name],
                                addName=Table.AddColumn(Table.SelectRows(
                                                            Table.SelectColumns([Content],{"Productos","Importes"}),
                                                            each List.Contains({"a","b"},[Productos])),
                                                        "fuente",
                                                        each cp)          
                            in 
                                addName),
    
    //finalmente usamos .Combine para anexar las Tablas resultantes de la columna recién creada
    AnexoTablas=Table.Combine(TrabajoTablas[Transforma])
in
    AnexoTablas

En este caso, se exige que todo el proceso de transformación se haga a nivel de fila, en cada una de las Tablas obtenidas en una nueva columna agregada:
- selección de columnas
- filtro de productos
- añadir nueva columna
todos pasos necesarios previos al anexado de las tablas resultantes.

Vamos con el segundo método, más clásico, donde la transformación se simplifica al expandir el contenido de una columna, momento en el que elegimos además, las columnas que necesitamos... y en un paso siguiente, aplicamos un filtro de los 'productos' deseados.
Igual que antes, añadimos una consulta en blanco y desde el editor avanzado escribimos:

let
    // recuperamos cualquier tabla, nombre definido, etc del libro de trabajo
    Origen = Excel.CurrentWorkbook(),
    //nos quedamos exclusivamente con aquellas que comienzen por 'Tbl'
    FilasFiltradas = Table.SelectRows(Origen, each Text.StartsWith([Name], "Tbl")),

    //usamos el botón de Expandir columna
    //o llamamos a la función .ExpandTableColumn
    //eligiendo columnas a recuperar
    ExpandimosContent = Table.ExpandTableColumn(FilasFiltradas, "Content", {"Productos", "Importes"}, {"Productos", "Importes"}),

    //por último aplicamos un filtro sobre el campo 'Productos'
    FiltroPdtos = Table.SelectRows(ExpandimosContent, each ([Productos] = "a" or [Productos] = "b"))
in
    FiltroPdtos

Nota:= este método permitiría emplear las opciones o botones del interfaz para proceder a la transformación... no requiriendo prácticamente nada de código.

Para esta alternativa, ahorrándonos mucho código y pasos, hemos simplificado para que la transformación quede en:
- expandir columna con la tablas contenidas, en cuyo paso, además, seleccionamos las columnas de origen deseadas. Como resultado disponemos de todas las tablas originales listadas...
- último paso para filtrar los productos elegidos sobre la totalidad de registros (+/- 18.000)

Al lanzar uno y otro proceso, no tan curiosamente, es más rápido el segundo (el que emplea Table.ExpandTableColumn), bastante lógico ya que el número de líneas de código generado es mucho menor, más simple y más ágil al no 'retorcer' el código con múltiples acciones (como tuvimos que realizar en el primer intento) para lograr lo que se podía conseguir en una sencila acción.

En definitiva, no se trata por tanto, de .Combine o .ExpandTableColumn, se trata de otros pasos de transformación necesarios para llegar a resultados equivalentes, y que retuercen (o no) en muchos casos nuestros pasos en las consultas que retrasan innecesariamente los tiempos de ejecución.

Seguir leyendo...

VBA: Función Filter sobre Arrays

Hoy un poco de VBA para Excel.
Hablaré de la función de VBA Filter(sourcearray, match, [ include, [ compare ]])
función que nos devuelve una matriz con aquellos elementos que cumplan las condiciones de coincidencia impuestas.
Los cuatro argumentos de la función a completar:
- sourcearray (obligatorio): Debe ser una matriz unidimensional de cadenas en la que se va a realizar la búsqueda.
- coincidencia (obligatorio): Qué buscamos.
- include (opcional): El valor Boolean que indica si se devuelven subcadenas que incluyan o excluyan la coincidencia.
Si incluir tiene el valor True, la función Filter devuelve el subconjunto de la matriz que contiene la coincidencia como una subcadena.
Si incluir tiene el valor False, Filter devuelve el subconjunto de la matriz que no contiene la coincidencia como una subcadena.
- compare (opcional): Valor numérico que indica el tipo de comparación de cadena que se va a usar.
Valores posibles de este argumento:
* vbUseCompareOption (ó -1): Realiza una comparación usando la configuración de la instrucción Option Compare.
* vbBinaryCompare (ó 0): Lleva a cabo una comparación binaria.
* vbTextCompare (ó 1): Lleva a cabo una comparación textual.

En definitiva la función filter aplicada sobre una matriz unidimensional devuelve una nueva matriz con el listado de coincidencias...

Veamos un uso diferente para esta función.
Tenemos un listado de países con ciertos importes asociados, y por otra parte otro listado de paises de los cuales queremos obtener su acumulado.

Así pues accederemos al editor de VB y en un módulo estándar insertaremos el siguiente código de nuestra función personalizada:

Function fxACUM(rngBuscados As Range, rngListado As Range, colSuma As Long)
'identificamos la matriz con los elementos buscados
Dim arrBuscados As Variant
arrBuscados = Application.Transpose(rngBuscados)

'y la matriz donde buscar
Dim arrListado As Variant
arrListado = Application.Transpose(rngListado)

'control num columna
If colSuma > UBound(arrListado) Then
    fxACUM = "num_col_superada"
    Exit Function
End If

'Dim arrPosicion() As Variant
Dim arrImporte() As Variant

i = 0
For elto = LBound(arrListado, 2) To UBound(arrListado, 2)
    Dim existe As Variant
    'función filter que devuelve una nueva matriz con los elementos que cumplan
    existe = Filter(arrBuscados, arrListado(1, elto), , vbTextCompare)
    
    'si tiene algún elemento
    If UBound(existe) >= 0 Then
        i = i + 1
        ReDim Preserve arrImporte(1 To i) As Variant
        'cargamos una matriz con los importes a acumular
        arrImporte(i) = arrListado(colSuma, elto)
    End If
Next elto

'retornamos la suma de la matriz...
fxACUM = Application.Sum(arrImporte)
End Function

Y como veíamos en la imagen anterior nos devuelve el resultado correcto de importes acumulados de los distintos paises elegidos.

El punto clave del procedimiento descrito es cuando se define una matriz 'existe' que cargamos con la matriz resultante de aplicar la función Filter sobre la matriz unidimensional de países buscados, y donde como elemento buscado damos cada uno de los paises, mediante el bucle For...Next, de la primera columna del rango de pais+importe.
Esto cargará con elementos siempre que haya coincidencia... en cuyo caso la función Ubound nos devolverá un valor distinto de cero... lo que aprovechamos con el condicional descrito para cargar ese dato en una matriz para los importes..
Matriz que será la que acabaremos sumando :O

Seguir leyendo...

Office Scripts: Los Objetos

Un objeto es una instancia definida por nostros que contiene un conjunto depares de valores. Estos valores pueden ser de cualquier tipo: valores escalares, funciones, matrices (tuplas también...) o incluso matrices de otros objetos...

El patrón que debemos seguir para crear un objeto

let nuestro_objeto = 
  {
    key1: 'valor_1',
    key2: 'valor_2',  
    key3: function () 
      {
        //functions
      },
    key4: ['elemento_1', 'elemento_2']
  };

Crear nuestros propios objetos nos abre un mundo de opciones para nuestros desarrollos.

La manera de trabajar sobre nuestros objetos y recuperar alguno de sus datos podría ser:

function main(workbook: ExcelScript.Workbook) {
  let nuestro_objeto: { anyo_nacimiento: number, nombre: string, apellidos: string[], edad: Function } =
  {
    anyo_nacimiento: 1971,
    nombre: 'Juan',
    apellidos: ['Gutiérrez', 'García'],
    edad: function(a: number) { return a - nuestro_objeto.anyo_nacimiento }
  };

console.log (nuestro_objeto.edad(2022) );
}

Como has podido ver en el ejemplo anterior, la forma de trabajar sobre nuestros datos 'cargados' en el objeto es llamando al objeto y tras escribir el punto la propiedad deseada (de entre las definidas).
Igual que ocurriría en cualquier otro lenguaje de programación...

En siguientes entregas repasaremos qué son y como definir las funciones en Typescript.

Seguir leyendo...

Office Scripts: Union Type

Un aspecto interesante de Office Scripts es que nos permite trabajar simultáneamente con distintos tipos de datos!!. Basta para ello emplear el operador de Union (para este lenguaje y caso es la barra vertical o 'pipe') en el momento que vayamos a defini. Una tupla es una Array pero con elementos con diferente tipo de datos.

Por ejemplo, en el siguiente código asignamos para una misma variable dos posibles tipos de datos permitidos:

function main(workbook: ExcelScript.Workbook) {
  let valor: string | number;
  valor = 13;
  console.log("trabajamos con valores numéricos como el: " + valor);

  valor = "Office Script";
  console.log("y al mismo tiempo con cadenas de texto como: " + valor);
}

No hay límite de asignación de tipos de datos en esa cadena de unión de tipos de datos, y además es compatible a la hora de definir Arrays!!.
Por ejemplo:

function main(workbook: ExcelScript.Workbook) {
  //declaramos una Array que puede ser numérica o de texto
  let arr: number[] | string[];
  let i: number;

  //primero trabajamos con números...
  arr = [1, 2, 4];
  console.log("Matriz numérica");
  arr.forEach (dato => {console.log (dato)});

  //luego cambiamos a texto... siendo la misma matriz!
  arr = ["España", "Portugal", "Italia","Francia"];
  console.log("Matriz de tipo texto");
  for (i = 0; i < arr.length; i++) 
  {
    console.log(arr[i]);
  } 
}

Una opción que no debes olvidar! ;-)

Seguir leyendo...

Office Scripts: las Arrays

Sin duda he sido siempre un 'enamorado' de las Arrays en VBA... y por simpatía, lo soy de las Arrays en Typescript (Office Scripts).
Las matrices nos permiten trabajar de formas distintas, aportándonos propiedades o métodos no disponibles en otros objetos (por ejemplo, los rangos)... por lo que siempre es buena idea estar familiarizados con ellas....

Debemos entender una Array como una colección de valores, que normalmente deben ser además del mismo tipo.

Empezaremos por determinar cómo declarar e inicializar una Array.
La forma más habitual es:

let arrnumeros: number[]; //declaración
  arrnumeros= [1,3,5];      //carga de datos o inicialización
  // o también en la misma línea
  let arrnumeros:number[]=[1,3,5];
  // o alternativamente de una forma más ortodoxa
  let arrnumeros: number[] = new Array(1, 3, 5);

Otro aspecto importante es cómo podemos acceder a esa matriz y recuperar alguno de sus datos.
para esto solo tenemos que referirnos al nombre de la Array declarado y entre corchetes indicar el número/posición del elemento a recuperar (OJOOOO, nos movemos en base cero!!).

 let arrnumeros: number[] = new Array(1, 3, 5);
  console.log (arrnumeros[2]);

que nos devolverá el TERCER elemento de nuestra Array!.

Pero como comentaba al inicio del artículo, la gran ventaja de las Arrays son los distintos métodos y/o propiedades de que disponen. Que a modo de resumen y en orden alfabético son:
I. miArray.concat(value1, value2, ..., valueN):
Devueve una nueva Array compuesta de los valores dados.
II. miArray.every(función_a _aplicar_a_cada_elemento, [opcional Objeto X])
Devuelve true si todos y cada uno de los elementos de la Array cumple la función dada.
III. miArray.filter(función_a _aplicar_a_cada_elemento, [opcional Objeto X]) Retorna una nueva Array con aquellos elementos que satisfagan la función dada.
IV. miArray.forEach(función_a _aplicar_a_cada_elemento, [opcional Objeto X]) Aplica a cada elemento la función dada.
V. miArray.indexOf(elemento_buscado, [opcional Base de numeración (0 por defecto)]) Obtenemos la posición del elemento buscado (la primera coincidencia!!) dentro de la Array. Devolverá -1 si no lo encuentra.
VI. miArray.join(separador) Concatena o une todos los elementos de la Array con el separador dado, retornando una cadena de texto.
VII. miArray.lastIndexOf(elemento_buscado, [opcional Base de numeración (0 por defecto)]) Obtenemos la posición del elemento buscado (la última coincidencia!!) dentro de la Array. Devolverá -1 si no lo encuentra.
VIII. miArray.map(función_a _aplicar_a_cada_elemento, [opcional Objeto X]) Crea una nueva Array con el resultado devuelto tras aplciar a cada elemento la función dada.
IX. miArray.pop() Elimina el último elemento de la Array, y además nos devuelve dicho elemento.
X. miArray.push(elemento1, ..., elementoN) Agregamos uno o más nuevos elementos al final de la Array, devolviendo además el nuevo tamaño.
XI. miArray.reduce(función_a _aplicar_a_cada_elemento, [opcional Valor Inicial]) Aplica la función dada al mismo tiempo contra dos elementos de la Array (de izquierda a derecha) hasta reducirlo a un único elemento.
XII. miArray.reduceRight() Igual que la anterior pero de derecha a izquierda.
XIII. miArray.reverse() Invierte el orden de los elementos.
XIV. miArray.shift() Elimina el primer elemento de la Array, y además nos devuelve dicho elemento.
XV. miArray.slice(inicio, [fin]) Devuelve una nueva Array con los elementos extraidos desde la posición de inicio hasta la de fin.
XVI. miArray.some(función_a _aplicar_a_cada_elemento, [opcional Objeto X]) Retorna true conque al menos uno de los elementos de la Array cumpla la función dada.
XVII. miArray.sort(Función_de_comparación) Devuelve una Array ordenada según la función dada... si omitimos la función se ordenan alfabéticamente.
XVIII. miArray.splice(index, cuántos, [opcional elemento1][opcionales , ..., elementoN]) Agrega y/o elimina elementos de la Array.
XIX. miArray.toString() Recuperamos una cadena de texto compuesta por los elementos de la Array.
XX. miArray.unshift(elemento1, ..., elementoN) Agregamos uno o más nuevos elementos al principio de la Array, devolviendo además el nuevo tamaño.

Sirvan a modo de introducción...
Un ejemplo de algunos de estos puntos comentados:

function main(workbook: ExcelScript.Workbook) {
  //crear Array vacía
  //let vacia:string[] = new Array();
  let vacia: string[] = [];   //alternativamente...
  console.log('"vacia" tiene:= num columnas ' + vacia[0]?.length + ' ' + 'num filas ' + vacia.length);

  //podemos definir una Array simple de una dimensión
  //let simple:string[] = new Array("ES","FR","IT","DE");
  let simple: string[] = [`ES`, `FR`, `IT`, `DE`] ;
  console.log('"simple" tiene:= num columnas ' + simple[0].length + ' ' + '\n num filas ' + simple.length);
  console.log('el array "simple" dispuesto: ' + simple);
  //podemos recuperar cualquiera de sus elementos (recuerda que nos movemos en base cero)
  console.log('el tercer elemento (base cero!) del array "simple": ' + simple[2]);

  //cambio el tamaño de la Array
  simple.length=2;  //OJO!, lenght permite redimensionar una Array
  console.log('el array "simple" modificado: ' + simple);
  simple[2]="UK";   //añadimos un elemento nuevo...
  console.log('el array "simple" con nuevo dato: ' + simple);

  //de forma similar con métodos como shift/unshift push/pop
  //que eliminan o agregan elementos por el principio o final de la Array
  simple.pop();   //elimina el último elemento
  console.log('el array "simple" despues de borrar el último elemento con pop: ' + simple);
  simple.shift(); //elimina el primer elemento
  console.log('el array "simple" despues de borrar el primer elemento con shift: ' + simple);
  simple.push('US');  //añade nuevo elemento al final
  console.log('el array "simple" despues de añadir nuevo elemento al final con push: ' + simple);
  simple.unshift('PT');   //añade nuevo elemento al inicio de la Array
  console.log('el array "simple" despues de añadir nuevo elemento al inico con unshift: ' + simple);

  //podemos ordenar nuestra Array con el método sort (en ASC por defecto) y reverse para invertir orden
  console.log("Array ordenada:= " + simple.sort());
  console.log("Array en orden inverso:= " + simple.reverse());

  //método join para unir los elementos del Array
  console.log("Array con sus elementos unidos:= " + simple.join(' y '));


  //o Arrays de 2D...
  let encab: string[][] = [['Fechas', 'País', 'Producto', 'Unidades']];

  console.log('"encab" tiene:= num columnas ' + encab[0].length + ' ' + 'num filas ' + encab.length);
  console.log('el array "encab" dispuesto: ' + encab);
}

Si vemos lo obtenido en el apartado de la 'consola' tendríamos:

"vacia" tiene:= num columnas undefined num filas 0
"simple" tiene:= num columnas 2 
 num filas 4
el array "simple" dispuesto: ES,FR,IT,DE
el tercer elemento (base cero!) del array "simple": IT
el array "simple" modificado: ES,FR
el array "simple" con nuevo dato: ES,FR,UK
el array "simple" despues de borrar el último elemento con pop: ES,FR
el array "simple" despues de borrar el primer elemento con shift: FR
el array "simple" despues de añadir nuevo elemento al final con push: FR,US
el array "simple" despues de añadir nuevo elemento al inico con unshift: PT,FR,US
Array ordenada:= FR,PT,US
Array en orden inverso:= US,PT,FR
Array con sus elementos unidos:= US y PT y FR
"encab" tiene:= num columnas 4 num filas 1
el array "encab" dispuesto: Fechas,País,Producto,Unidades

Finalizar con un nombre raro a priori, pero de concepto simple: las tuplas. Una tupla es una Array pero con elementos con diferente tipo de datos.
Por suerte nos servirán igualmente todos los aspectos comentados (creación, métodos, etc...)

Seguir leyendo...

Office Scripts: Tipo de dato Strings básicos

Dentro de los tipos de datos 'primitivos' (digamos son los esenciales: number, boolean, string, symbol, void, null y undefined - revisa este post) tenemos el tipo de dato String, seguramente el más habitual...
Este tipo de datos tiene algunas peculiaridades y aspectos fundamentales que debemos conocer.

Lo primero es cómo podemos declarar una variable como tipo String...
Pues tenemos basicamente tres formas.
1-Una empleando la comillas dobles
2-comillas sencillas
3-comillas invertidas (en realidad es una tilde aguda)

function main(workbook: ExcelScript.Workbook) {
    let texto: string = "para textos normales";      //con comillas dobles
    let texto_dos: string = 'y estas también';  //con comillas simples
    let texto_tres: string = 'pero si queremos ser especiales...';  //con comillas invertidas (en realidad es una tilde!!)
}

Las comillas dobles y las sencillas básicamente hacen la misma cosa... trabajan con cadenas de texto. Fiables y clásicas.
Pero las comillas invertidas nos permiten embeber entre ellas otras expresiones e incluso saltos de líneas directamente!!

function main(workbook: ExcelScript.Workbook) {
    //ejemplo del potencial de las comillas invertidas
    let lenguaje: string = "TypeScript";
    let anyo: number = 2022;
    let unido: string = `En este año ${anyo},
    deberías aprender un poquito de ${lenguaje}`;
    console.log (unido)
}

Fíjate en la facilidad para dar saltos de línea o incluir otras expresiones!!

La alternativa clásica nos llevaría a este código:

function main(workbook: ExcelScript.Workbook) {
    //ejemplo del potencial de las comillas invertidas
    let lenguaje: string = "TypeScript";
    let anyo: number = 2022;
    let unido: string = `En este año ${anyo},
deberías aprender un poquito de ${lenguaje}`;
    console.log (unido);
    let unido_old: string = 'En este año ' + anyo + ', \n deberías aprender un poquito de ' + lenguaje ;
    console.log(unido_old);
}

Notemos que el salto de línea en este caso se consigue con la expresión \n y como debemos insertar el resto de variables con el operador +
Por cierto, otro caracter especial interesante de conocer es el TABulador: \t

Una propiedad clásica de los textos nos permitirá conocer el número de caracteres que lo compone. La propiedad .length bos devuelve esta información...

function main(workbook: ExcelScript.Workbook) {
    //ejemplo del potencial de las comillas invertidas
    let lenguaje: string = "TypeScript";
    let anyo: number = 2022;
    let unido: string = `En este año ${anyo},
deberías aprender un poquito de ${lenguaje}`;
    console.log (`${unido} y número de caracteres ${unido.length}`);
    let unido_old: string = 'En este año ' + anyo + ', \n deberías aprender un poquito de ' + lenguaje ;
    console.log(unido_old + 'y numero de caracteres ' + unido_old.length);
}

Otra posibilidad que se nos ofrece es retornar el caracter de una posición en concreto... para lo que usaremos los corchetes ( [] ) o alternativamente el método charAt. Por ejemplo:

function main(workbook: ExcelScript.Workbook) {
    //ejemplo del potencial de las comillas invertidas
    let lenguaje: string = "TypeScript";
    let anyo: number = 2022;
    let unido: string = `En este año ${anyo},
deberías aprender un poquito de ${lenguaje}`;
   
   console.log (unido[0]);  //recuerda siempre base cero!
   console.log (unido.charAt(0));  //recuerda siempre base cero!
}

Otros métodos muy obvios para los textos...
Por ejemplo para convertir en mayúsculas o minúsculas todos los caracteres alfabéticos:
.toUpperCase y .toLocaleUpperCase
.toLowerCase y .toLocaleLowerCase

O el clásico .IndexOf similar a la función ENCONTRAR o HALLAR de la hoja de cálculo.
Nos devolverá la posición donde encuentre la subcadena buscada. Nos podremos apoyar en el segundo parámetro para discriminar posibles coincidencias...

function main(workbook: ExcelScript.Workbook) {
    //ejemplo del potencial de las comillas invertidas
    let lenguaje: string = "TypeScript";
    let anyo: number = 2022;
    let unido: string = `En este año ${anyo},
deberías aprender un poquito de ${lenguaje}`;
   
   console.log (unido.indexOf("año")); 
}

Existen muchos más métodos habituales en otros entornos..
Por ejemplo:
1- .includes(subcadena, posicion_inicial_busqueda) devuelve true si la encuentra.
2- .startsWith(subcadena, posicion_inicial_busqueda) devuelve true si comienza por ésta.
3- .endsWith(subcadena, posicion_inicial_busqueda) devuelve true si termina por ésta.
4- .replace(subcadena_buscada, reemplazada_por) devuelve el nuevo texto ya cambiado.
5- .slice(posición_inicial, hasta_posición) devuelve la parte extraida (como la función EXTRAE).
6- .substr(posición_inicial, número de caracteres) devuelve la parte extraida (como la función EXTRAE).
7- .split(separador) devuelve una Array con las partes del texto divididas por el separador indicado....
y un largo etcétera ;-)

Seguir leyendo...

Power Query: Diferencia dinámica entre columnas

Días atrás se me planteó si habría alguna posibilidad, empleando Power Query, de automatizar el cálculo entre las distitnas columnas de unas tablas dadas, a partir de otros listados personalizados de columnas con las que operar.

Tenía los datos, tenía las reglas a seguir.. asi que empecé el análisis.

En un primer paso, obviamente, realicé la carga de las tablas implicadas:
-Dos con los datos sobre los que operar llamadas: dData1 y dData2
-Otras dos con la lista de campos a restar uno-a-uno: lList1 y lList2

El segundo paso ya dentro del Editor de Power Query consiste en unir fila por fila las dos tablas principales dData1 y dData2, por lo que opté por la forma clásica:
1-añadir una columna de índice en cada tabla,
2-para luego realizar una combinación fila a fila entre ambas...
El código generado con el asistente quedó entonces:

let
    //OJO!, en la carga de las tablas dData1 y dData2 se ha añadido una columna Índice !!
    Origen = Table.NestedJoin(dData1, {"Índice"}, dData2, {"Índice"}, "dData2", JoinKind.LeftOuter),
    Se_expandio_dData2 = Table.ExpandTableColumn(Origen, "dData2", {"A2", "B2", "C2"}, {"A2", "B2", "C2"}),
    Columnas_quitadas = Table.RemoveColumns(Se_expandio_dData2,{"Índice"})
in
    Columnas_quitadas

OJO!!. recuerda que al momento de cargar las dos tablas de datos (dData1 y dData2) se le han agregado sendas columnas de Índice!!

En el tercer paso preparamos un listado único, a partir de las dos listas de columnas (lList1 y lList2), con la que tendremos un listado de restas entre columnas...

let
    //empleamos la función List.Zip para entretejer los elementos de ambos listados
    Origen = List.Zip({lList2[List2],lList1[List1]}),

    //para acabar montando una cadena de texto que 'simule' una operación entre columnas
    Unido=List.Transform(Origen, each "[" & Text.Combine( _  ,"]-[") & "]" )
in
    Unido

Empleamos List.Zip para cruzar elemento a elemento los datos de ambas listas...
Obteniendo una Lista de listas, sobre la que trabajamos para resultar una cadena de texto (con Text.Combine) que se asemeje a lo que escribiriamos si trabajaramos entre columnas...

Ya tenemos los datos unidos de mabas tablas, y tenemos los cruces entre columnas para realizar ese cálculo de la diferencia/resta.
Último paso. Emplearemos la función List.Accumulate para agregar de forma dinámica tantas nuevas columnas como surjan de la combinación de las tablas lList1 y lList2 (puedes ver un ejemplo similar en este artículo).

let
    Origen = #"dData1+dDdata2",
    //https://blog.excelforo.com/2021/11/power-query-operar-sobre-columnas.html
    //List.Accumulate(list as list, seed as any, accumulator as function) as any
    //Acumula un valor de resumen de los elementos de la lista list, mediante accumulator. 
    //Se puede establecer un parámetro de inicialización opcional, seed.
    AddCols =  List.Accumulate(
                List_Dif,
                Origen,
                (tabla_actual, nueva_col) =>
                    if List.Contains(Table.ColumnNames(tabla_actual), nueva_col) then
                        tabla_actual
                    else 
                        Table.AddColumn(tabla_actual, 
                                        nueva_col, 
                                        each Expression.Evaluate(nueva_col,[_ = _]))  )
in
    AddCols

Con List.Accumulate realizamos un recorrido por la recien generada Lista de diferencias entre columnas:
[A2]-[A1]
[C1]-[B1]
[B2]-[C2]

verificando con el condicional if...then..else... si la columna a agregar ya existiera, en caso negativo, empleamos Table.AddColumn para dicha acción.
Lo interesante es qué valor añadimos para cada fila de esa nueva columna... donde aprovechándonos del elemento construido (recuerdas el que simulaba una resta entre columnas?) y aplicando Expressión.Evaluate, indicando un entorno de trabajo controlado bajo las condiciones del bucle ([_ = _] y no #shared puesto que son situaciones virtuales momentáneas...

El resultado final es, por tanto, el deseado una vez cargada y cerrada la consulta y devuelta a la hoja de cálculo:

Seguir leyendo...

Office Scripts: Ejecución desde tu escritorio

Maravilloso!!
Hace unos días Microsoft liberó una funcionalidad (deseada por muchos) que va a cambiar nuestra forma de trabajar...
Y es que desde hace algunas semanas Microsoft permite que los Scripts que hubieras creado (en tu Excel Web) y a los que hubieras asociado un botón de ejecución (si... esto es posible desde hace algunos meses!!) los podamos ejecutar también desde nuestra versión de escritorio en Windows...
Además tendrás la posibilidad de abrir el Script en modo lectura!!.

Esto abre un mundo infinito para la interacción global con multiusuarios sobre nuestros ficheros subidos a Onedrive o Sharepoint... y nos da una señal de aviso... aprende Office Scripts (Typescript) ;-)

Veamos un ejemplo de como gestionar esto...
Paso 1. Si no lo tienes ya, crea un Script en un libro de tu OneDrive (o Sharepoint)
Algo sencillo nos servirá... por ejemplo escribir algo en una celda:

function main(workbook: ExcelScript.Workbook) {
    let miRango=workbook.getActiveWorksheet().getRange("A1");
    let miCelda=miRango.setValue("Texto generado con un Script de Excel Web")
}

Paso 2. Asociemos el Script a un botón (seguimos trabajando en Excel Web).
Para ello, tras guardar el Script, buscaremos los tres puntitos arriba a la derecha en el panel del editoro de código
Luego elegiremos la opción Botón Agregar

Esto añadirá en la hoja de cálculo nuestro deseado botón!!... al cual podremos añadir un texto personalizado (clic derecho del ratón y Editar texto)

Paso 3. Puesto que estamos trabajando en la nube no hará falta guardar... solo abre el fichero con la edición de escritorio..
-bien desde el desplegable de Edición arriba en la cinta de opciones... deplegar Edición y elegir 'Aplicación de escritorio'

-bien directamente cierra Excel Web, accede a tu Onedrive (en la web o en local si lo tienes sincronizado) y ábrelo normalmente.. se abrirá con tu aplicación de escritorio

Paso 4. Ya estamos en nuestra aplicación de escritorio...
OJOOOO, según la documentación de Microsoft para poder hacer uso de estos Scripts se necesita:
-Excel en la Web (Excel para Windows solo puede usar Scripts de Office con botones de script).
-OneDrive para la Empresa.

Cualquier licencia de Microsoft 365 comercial o educativa con acceso a las aplicaciones de escritorio de Microsoft Office 365, como:
Office 365 Empresa
Office 365 Empresa Premium
Office 365 ProPlus
Office 365 ProPlus para dispositivos
Office 365 Enterprise E3
Office 365 Enterprise E5
Office 365 A3
Office 365 A5

Esto es lo que vemos... NUESTRO BOTÓN disponible!

Paso 5. Lo mejor viene ahora... presionamos el botón y ... la magía apareció.
El Script se ha ejecutado como esperábamos, ha escrito nuestro texto en la celda A1!!
Además ha aparecido un Panel 'estado de ejecución de script' desde el cual podemos abrir en modo lectura el código de nuestro Scripts!!
Al presionar el cuadro donde aparece el nombre del Script, se abrirá un nuevo panel de 'Edición de código', desde donde poder ver información del Script y con un botón de Editar que nos permite leer el script (de momento solo leer... pero seguro que en poco tiempo editar y crear.. ojalá!)

Muchos grandes avances se están aportando a la versión Excel web, esta apuesta de Microsoft solo nos puede dar grandes alegrías ;-)

Seguir leyendo...