Office Scripts: Trabajando con Tablas

Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño vicio)... pero hay que liberar la cabeza con otras heerramientas :D
En esta ocasión os planteo generar un código con Office Scripts que recorra las distintas hojas de un libro, buscando las 'Tablas' que hubiera en cada hoja, para finalmente trasladar el resultado a una sola hoja 'Resumen', con los valores anexados...
Lo que viene siendo un clásico de la programación.

Me parece un código interesante por que emplea las sentencias más habituales como condicionales o bucles entre las colecciones de hojas o celdas... Una recopilación de los diferentes artículos publicados sobre el tema en este blog.
Vamos con el código.
Aprovechando que hace poco se liberó la ficha 'Automatizar' en la versión de escritorio, y que este código será 100% compatible con la versión Web, emplearemos la opción.

En la Ficha 'Automatizar' buscaremos el botón de 'Nuevo Script', lo que nos abrirá el panel de Editor de código donde escribiremos nuestro código...
No hay grandes diferencias entre esta pestaña de la versión de escritorio con la de la versión web.

En el editor de código escribiremos:

function main(workbook: ExcelScript.Workbook) {
let hojas = workbook.getWorksheets();
let encab: string[][] = [['Fechas', 'País', 'Producto', 'Unidades']];
  console.log ('num columnas ' + encab[0].length);
  console.log('num filas ' + encab.length);

let arrFin:string[][] = [];   //array 2D - dos dimensiones
    //bucle por todas las hojas del libro
  hojas.forEach(hoja  => {
    let tablas=hoja.getTables();  //identificamos las tablas de cada hoja
    //bucle por las tablas
    tablas.forEach(tbl => {
      //trabajamos con todos los datos de la tabla (sin encabiezados ni fila totales)
      let colsUds=tbl.getRangeBetweenHeaderAndTotal().getValues();
      
      //filtramos solo los registros cuyo valoe en Unidades > 50 (columna 4 = 3 en base cero)
      //el resultado es otra ARRAY
      //let filtrado = colsUds.every( valor => valor[3] < 50); //alternativa a .filter
      let filtrado = colsUds.filter( valor => valor[3] > 50);
      
      //anexamos-agregamos cada nueva array a las anteriores
      arrFin = [].concat(arrFin,filtrado);
    })
  });

console.log (encab);
console.log (arrFin);
//devuelvo la matriz filtrada a la hoja REsultado
let rdo=workbook.getWorksheet("Resultado")
rdo.getRange("A1:D1").setValues(encab);
rdo.getRange("A2:D" + (arrFin.length+1)).setValues(arrFin);
};

Las etapas del proceso están explicadas/comentadas...
Lo interesante es que la filosofía o la idea detrás del código coincide con la que podrías aplicar con código VBA 😊...
Y el resultado es el esperado.

Mencionar algunas propiedades y métodos del objeto Tabla para Office Scripts... algunos empleados en el Script anterior.
Nos adentramos en el interface de ExcelScript.Table!!
La verdad es que es difícil decidir cuál de los muchos métodos y propiedades mostrar (existen algo más de cuarenta!! :O), asi que iré a los clásicos.

addTable(range, true/false): Creamos una tabla nueva con o sin encabezado.
addColumn(index, values, name): Agrega una nueva columna a la tabla.
addRow(index, values) Agrega una fila a la tabla.
delete() Elimina la tabla.
deleteRowsAt(index, count) Elimine un número especificado de filas en un índice determinado.
getHeaderRowRange() Obtiene el objeto de intervalo asociado a la fila de encabezado de la tabla.
getName() Nombre de la tabla.
getRange() Obtiene el objeto de rango asociado a toda la tabla.
getRangeBetweenHeaderAndTotal() Obtiene el objeto de rango asociado al cuerpo de datos de la tabla.
getRowCount() Obtiene el número de filas de la tabla.
getTotalRowRange() Obtiene el objeto de intervalo asociado a la fila de totales de la tabla.
resize(newRange) Cambie el tamaño de la tabla al nuevo intervalo. El nuevo intervalo debe superponerse con el intervalo de tabla original y los encabezados (o la parte superior de la tabla) deben estar en la misma fila.
setName(name) Nombre de la tabla.

Unos ejemplos de esto sería el siguiente código:

function main(workbook: ExcelScript.Workbook) {
  let tWb=workbook;
  let hoja = tWb.getWorksheet("Tablas");
  //creamos nuestra tabla...
  let ejemtabla: ExcelScript.Table =  hoja.addTable("A1:D1", true /*con encabezados*/);

  //le damos nombre
  ejemtabla.setName("Mi_Tabla");

  //incorporamos lso títulos de los campos
  ejemtabla.getHeaderRowRange().setValues([["Fecha", "Producto", "Unidades", "Importe"]]);

  //añadimos un conjunto de filas
  ejemtabla.addRows(null /*agrega un conjunto de filas al final de la tabla*/, 
  [
    ["1/1/2023", "Pdto A", 103, "123.45"],
    ["2/15/2023", "Pdto B", 22, "987.65"],
    ["5/21/2023", "Pdto C", 12, "654.02"]
  ]);

  //Añado una nueva columna___________________________________________
  //indicamos posición de la nueva columna - null para añadirla al final
  //después una matriz que incluya posiciones para encabezados y filas
  //y por úlitmo el título para el campo/columna
  ejemtabla.addColumn(4,["","1","5","6"],"Descuento")

  //Inserto una única fila nueva en la primera posición_________________
  //indicamos la posicón de la fila a insertar
  //y luego el vector-matriz de datos
  ejemtabla.addRow(0,["09/13/2023","Pdto DD",13,"111.11","9"])

  //recuperamos los distintos elementos_________________________________
  //el encabezado
  let encab=ejemtabla.getHeaderRowRange().getValues()
  console.log(encab)

  //el nombre de la tabla
  let nombre=ejemtabla.getName()
  console.log(nombre)

  //el rango que ocupa toda la tabla
  let rngTabla=ejemtabla.getRange().getAddressLocal()
  console.log(rngTabla)

  //el número de filas de la tabla
  let numfilas=ejemtabla.getRowCount()
  console.log(numfilas)

  //recuperamos una matriz con el contenido solo de la parte de datos
  //sin encabezado ni fila de totales
  let databody=ejemtabla.getRangeBetweenHeaderAndTotal().getValues()
  console.log(databody)

  //redimensionamos el tamaño de la tabla, 
  //dos columnas y tres filas mas
  ejemtabla.resize(ejemtabla.getRange().getResizedRange(3, 2).getAddressLocal())
  console.log(ejemtabla.getRange().getAddressLocal())

  //eliminamos la fila 5 y 6___________________________________________
  ejemtabla.deleteRowsAt(4,2)

  //eliminamos la tabla____________________________________________
  ejemtabla.delete()
}

El resultado previo a eliminar la tabla (último paso) es:

Ten presente que para trabajar a nivel de columnas deberás, en algunas ocasiones, hacer uso del interface ExcelScript.TableColumn!!!
Por suerte los métodos y propiedades en este caso son solo diez ;-)
delete() Elimina la columna de la tabla.
getFilter() Recupera el filtro aplicado a la columna.
getHeaderRowRange() Obtiene el objeto de rango asociado a la fila de encabezado de la columna.
getId() Devuelve una clave única que identifica la columna de la tabla.
getIndex() Devuelve el número de índice de la columna dentro de la colección de columnas de la tabla. Indizado con cero.
getName() Especifica el nombre de la columna de tabla.
getRange() Obtiene el objeto de rango asociado a toda la columna.
getRangeBetweenHeaderAndTotal() Obtiene el objeto de rango asociado al cuerpo de datos de la columna.
getTotalRowRange() Obtiene el objeto de rango asociado a la fila de totales de la columna.
setName(name) Especifica el nombre de la columna de tabla.


Además de los métodos concretos dentro de ExcelScript.Table como pueden ser:
getColumn(key) Obtiene un objeto de columna por nombre o identificador. Si la columna no existe, este método devuelve undefined.
getColumnById(key) Obtiene un objeto de columna por identificador. Si la columna no existe, devolverá undefined.
getColumnByName(key) Obtiene un objeto de columna por Name. Si la columna no existe, devolverá undefined.
getColumns() Representa una colección de todas las columnas de la tabla.
y del ya comentado
addColumn(index, values, name): Agrega una nueva columna a la tabla.

Un ejemplo lo podemos ver en el siguiente código:

function main(workbook: ExcelScript.Workbook) {
  let tWb = workbook;
  let hoja = tWb.getWorksheet("Tablas");
  //definimos una variable que corresponda con nuestra tabla...
  let ejemtabla: ExcelScript.Table = hoja.getTables()[0];
  console.log (ejemtabla.getName());

  //defino una variable de la 'Importe'
  let col:ExcelScript.TableColumn = ejemtabla.getColumnByName('Importe');

  //recuperamos la posición de la columna
  let posicion=col.getId();
  console.log(posicion);

  //Cambiamos el nombre de la columna
  col.setName("Importe_EUR");

  //Obtenemos el rango, como string, de celdas correspondiente a la columna
  let rango=col.getRangeBetweenHeaderAndTotal().getAddressLocal();
  console.log(rango);

  //O bien como Array de valores
  let arrCol=col.getRangeBetweenHeaderAndTotal().getValues();
  console.log(arrCol);

  //podríamos tambien eliminar la columna identificada
  //col.delete ()
}  

El efecto del trabajo lo vemos en la imagen siguiente:

Espero sirva como breve introducción al trabajo con tablas en nuestras hojas de cálculo con Office Scripts.

Seguir leyendo...

Power Query: Tablas a partir de texto

Una situación no muy habitual es tener que procesar un texto como si fuera una tabla (u otro objeto) dentro de Power Query... Y esto es lo que voy a exponer en el artículo de hoy.
Veremos cómo a partir de los elementos de texto de una lista, realizaremos ciertas transformaciones, hasta conseguir anexar las tablas existentes correspondientes a cada literal.

Veamos en la imagen siguiente el punto de partida (y el resultado final obtenido).
Tenemos tres tablas ('TblES', 'TblDE' y 'TblIT'), pero podrían ser indeterminadas... Y necesitamos anexarlas en una única tabla final.
Como dificultad añadida tenemos encabezados diferentes.

Como siempre cargaremos nuestros origenes (las tres tablas, mas una cuarta con la traducción de los nombres de columnas) y crearemos en el Editor de Power Query una consulta en blanco con el siguiente código M:

let
    lstNombres={"TblES","TblDE","TblIT"},
    lstNuevosNombres={"Date","Units","Amount"},

    //generamos una tabla de null, que será la 'semilla' de List.Accumulate
    tblINI=#table(lstNuevosNombres,{List.Repeat({null}, List.Count(lstNuevosNombres))}),

    //recorremos los nombres de las tablas
    anexado=List.Accumulate(
            lstNombres,     //elementos a recorrer
            tblINI,         // semilla o inicio de la secuencia
            (state,current)=> let
                //paso clave para trasnformar texto como Tabla
                tabla="#""" & Expression.Identifier(current) & """",

                tbl=[MiTabla= Expression.Evaluate(tabla,#shared)],
                TablaMedia=tbl[MiTabla],    //Obtengo una Tabla a partir de un elemento texto

                //Tranformaciones una vez convertido el texto a tabla_____________
                //selecciono campos según TblLANG
                lang=Text.End(current,2),
                tsl=Table.SelectRows(TblLANG, each List.Contains(lstNuevosNombres,[UK])),
                lstCampos=Table.Column(tsl, lang),
                //preparo lista 'zippeada' para renombrar columnas
                zip=List.Zip({lstCampos,lstNuevosNombres}),
                TblRenombroCOLS=Table.RenameColumns(TablaMedia,zip),
                //y acabo seleccionando solo las columnas requeridas
                TablaCOLS=Table.SelectColumns(TblRenombroCOLS,lstNuevosNombres),
                //incorporo una nueva col con el país..
                TablaFinal=Table.AddColumn(TablaCOLS, "Country", each lang)
            in 
                Table.Combine({state,TablaFinal})
    )
in
    anexado

Me interesa que nos detengamos en el 'paso clave':

...
    //paso clave para trasnformar texto como Tabla
	tabla="#""" & Expression.Identifier(current) & """",

	tbl=[MiTabla= Expression.Evaluate(tabla,#shared)],
	TablaMedia=tbl[MiTabla],    //Obtengo una Tabla a partir de un elemento texto
                ...

Empezamos a construir un literal de cómo entendería o nombraría el motor M a una de nuestras tablas:
Empleamos la función ya conocida Expression.Identifier, además, y esto es importante, concatemos el texto con el caracter almohadilla!!.
En el paso siguiente, creamos un registro asignándole como valor el objeto obtenido al aplicar Expression.Evaluate, que transforma el texto en el objeto que representa, una tabla en el ejemplo.

MUY importante la identificación de los 'contextos'.
Acabamos recuperando el objeto cargado en el registro, siendo ya una tabla al 100%.

El bucle aplicado con List.Accumulate se encarga de ir anexando una tabla trsa otra...

Obviamente este no es el mejor método para anexar tablas... pero bajo ciertas condiciones, el método puede ser interesante.
Abierto a escuchar comentarios y sugerencias de mejora. ;-)

Seguir leyendo...

Power Query: Reagrupar por fila

Recientemente me topé con un problema curioso, fácil de resolver en la hoja de cálculo, pero no tan sencillo (diría yo) de hacerlo con Power Query.
Se trata de recorrer cada fila de nuestra tabla e ir agrupando conceptos iguales.

El ejemplo sobre el que voy a basar la explicación es bastante gráfico.
Tenemeos un listado de facturas, donde cada una de ellas tiene distintos tipos de IVA aplicados... pero en posiciones/columnas dispares.
Lo vemos en la imagen siguiente:

Si nos fijamos en la imagen, en concreto en la segunda factura, veremos que devuelve registros con dos tipos de IVA en tres movimientos, colocados 'aleatoriamente' en las distintas columnas... una base imponible al 10% y otras dos al 21%.
El objetivo es distribuir y ubicar, de manera homogénea, todas las bases al 4% en la misma columna, las bases al 10% en su propia columna, e igual con las bases al 21%.., esto es, reagrupar los elementos de la misma fila...

Como siempre cargaremos nuestro origen y editaremos la consulta con el siguiente código M:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
 
    lstTipos={"Tipo_1","Tipo_2","Tipo_3"},
    lstCamposCrear={"BI4","BI10","BI21"},
    lstPorc={0.04,0.10,0.21,0.00},
    
    //definimos el límite con un valor
    Contador=List.Count(lstCamposCrear),

    //creamos una función recursiva que recorra cada fila/registro
    RepartoBases=(TablaTemp as table, n as number)=> 
            let TablaFinal=Table.AddColumn(
                TablaTemp, //trabajamos siempre sobre la tabla modificada
                lstCamposCrear{n},  //asignamos un nombre de columna asociada a las listas previas
                each let rw=_ in    //sobre el registro que representa cada fila
                                    //aplicamos ciertas transformaciones
                                    //que nos permiten obtener el dato de la columna buscada,,,
                   try List.Sum(List.Select( 
                            List.Transform(lstTipos, 
                                each 
                                let 
                                    i=Text.End(_,1)
                                in 
                                    if Record.FieldOrDefault(rw,_)=lstPorc{n} then 
                                            Record.FieldOrDefault(rw,"Base_" & i) 
                                    else 0),
                        each _<>0))
                    otherwise 0)
        in 
            //controlamos la finalización del proceso cuando lleguemos al último reemplazamiento
            if n+1=Contador then TablaFinal // terminamos
                else @RepartoBases(TablaFinal,n+1),   // mantenemos el ciclo de sustitución
            
    //aplicamos nuestra función recursiva anterior sobre la Tabla Original
    Resultado=RepartoBases(Origen, 0)

in
    Resultado

El código es algo largo, pero efectivo, ya que nos devuelve lo deseado...
De manera recursiva se ha procedido a crear las tres columnas necesarias (una por cada tipo de IVA) e ir incorporando como dato, el aacumulado/suma de las distintas bases coincidentes con dicho tipo.

En este ejemplo me he apoyado en la función Record.FieldOrDefault que permite recuperar el dato de un registro para un campo concreto... interesante cunado trabajamos por 'filas'.
Espero te resulte de utilidad, y por supuesto, si conoces alguna forma alternativa... será un placer compartirla.

Seguir leyendo...

Power Query: Extraer fecha de un texto

Una situación tediosa, en la que me he visto frecuentemente envuelto, es la de recuperar fechas contenidas en una cadena de texto

En el supuesto de hoy expondré la solución, que después de varios intentos, he desarrollado como más óptima...
Seguro que los expertos en la materia encontrarán una alternativa más eficiente y corta...

Si bien, mi ejemplo me parece interesante por el uso de alguna función poco habitual como por ejemplo:
Text.ToList
que tiene la peculiaridad de 'desmenuzar' un texto en cada uno de sus caracteres.
Esto nos permite descubrir si en una cadena de texto existe algún caracter empleando, por ejemplo. otra función curiosa:
List.ContainsAny
que localiza elementos de la segunda lista en la primera...

Mencionadas estas dos funciones, podemos desarrollar nuestro código M.

A partir de la carga de la tabla original con nuestros texto, continuaremos la consulta con el siguiente código:

let
    //cargamos los datos..
    Origen = Excel.CurrentWorkbook(){[Name="TblFechas"]}[Content],
    //añadimos una columna con el cálculo deseado
    ToC=Table.AddColumn(Origen,
            "Extrae_Fecha",
            each 
                let
                    //Limpiamos de caracteres no deseados nuestras cadenas de texto
                    //Usamos Text.Select para detrerminar qué caracteres permanecen
                    txt=Text.Select(Text.From([col1]),{"0".."9","/","-",".","|"," "}),
                    //separamos el texto resultante por un separador que se repite en el ejemplo
                    //dependerá de la cadena de texto...
                    partes=Text.Split(txt,"|"),

                    //realizamos una selección de las partes obtenidas
                    //pero solo cuando concurran dos condiciones:
                    //1-que el texto resultante tenga más de 8 caracteres
                    //      (suponemos que cuanlquier fecha, al menos, tiene
                    //          un patrón dd/mm/aa
                    //          o bien dd/mm/aaaa - con 10 caracteres)
                    //2-otra condición es que la parte de texto a analizar tendrá dos veces el separador de fecha
                    //      (alguno de los habituales: . / -
                    selecc=Text.Trim(
                            List.Select(partes, 
                                each 
                                    List.Count(Text.ToList(_))>=8 and 
                                    List.Count(List.PositionOfAny(Text.ToList(_),{".","/","-"},Occurrence.All))=2){0}),

                    //Una última criba donde permanece la primera 'subparte'
                    tb=Text.BeforeDelimiter(selecc," "),
                    calc=if tb="" then selecc else tb
                in 
                    //finalmente aplicamos la conversión de la fecha
                    // aplicando la 'cultura' de fecha en español
                    try Date.FromText(calc,[Culture="es-ES"]) otherwise null)[[Extrae_Fecha]]
in
    ToC

El resultado, a priori, parece el deseado ;-)

Seguir leyendo...

Power Query: Table.Profile y cómo agregar tus cálculos

En la entrada previa veíamos una funcionalidad básica, pero muy potente, de Table.Profile... y adelantaba una opción que nos permite dicha función, como es crear nuestros propios cálculos agregados sobre las columnas de la tabla.
La función tiene la siguiente sintaxis:
Table.Profile(table as table, optional additionalAggregates as nullable list) as table
Y para incorporar el segundo argumento opcional: additionalAggregates deberemos crear una lista con la siguiente estructura:
1- Nombre de la nueva columna
2- definición de sobre qué tipo de dato va a trabajar la columna. Normalmente emplearemos la función:
Type.Is(type1 as type, type2 as type) as logical
de tal forma que solo se trabajará sobre elementos del tipo descrito en la columna.
3- la función de agregado que operará sobre todos los valores de la columna. Habitualmente serán funciones de tipo List (como List.Count, List.Average, List.Median, List.Sum, etc...).

En el ejemplo de hoy realizaremos una búsqueda de un texto para determinar en qué columna se encuentra.
Seguro que muchas veces has necesitado localizar algo en una tabla, y sabrás lo 'pesado' que se hace ubicar una búsqueda en alguna columna... Table.Profile te ahorrará mucho trabajo ;-)

Partiremos del rango siguiente, sobre el que queremos localizar en qué columnas aparece el texto 'Total' (en cualquiera de sus formas)

Cargamos el rango al editor de consultas y escribimos...

let
    Origen = Excel.CurrentWorkbook(){[Name="ndtest"]}[Content],
    //creamos nuestro propio cálculo agregado
    //que nos indicará en qué columnas existe el texto 'Total' en cualquiera de sus formas
    TP = Table.Profile(Origen, 
                { {"contiene", 
                    each Type.Is(_, type any), 
                    each let col=_ ,
                        rdo=List.AnyTrue( List.Transform(col, each Text.Contains(_,"Total", Comparer.OrdinalIgnoreCase)))
                    in 
                        try rdo otherwise false }  }) [[Column],[contiene]],

    //filtramos para obtener únicamente las columnas donde
    //nuestro cálculo sea cierto
    ColsTOTAL = Table.SelectRows(TP, each ([contiene] = true))[Column],

    //Seleccionamos dichas columnas con el texto buscado
    TablaTOTAL=Table.SelectColumns(Origen,ColsTOTAL)
in
    TablaTOTAL

La estructura del argumento de Agregación, para crear nuestro propio cálculo, ha sido:

...
    { {"contiene", 
        each Type.Is(_, type any), 
        each let col=_ ,
                 rdo=List.AnyTrue( List.Transform(col, each Text.Contains(_,"Total", Comparer.OrdinalIgnoreCase)))
              in 
                 try rdo otherwise false }  }
    ...

En la primera parte dábamos el nombre a la futura nueva columna ('contiene' en mi ejemplo)
En la segunda indicamos qué tipo de datos admitimos para el siguiente cálculo. Aquí podríamos indicar cualquier tipo de dato más preciso (number, logical, text, etc...)
Y por fin, en la tercera parte, indicamos el cálculo sobre cada columna. Operamos sobre cada columna, transformada en Lista... Y sobre cada elemento de esa lista, procesamos a su vez, una verificación de Texto.
El objetivo es obtener para cada columna un true o un false según contenga en alguno de sus elementos el texto buscado...

Creo que esta es la forma más sencilla que he trabajado para identificar columnas :OO

Seguir leyendo...