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.

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.

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

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.

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)

Seguir leyendo...