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...

Power Query: Table.Profile para eliminar columnas vacías

Uno de los problemas habituales con los que nos encontramos es eliminar las columnas sin datos, suele ser una tarea tediosa que requiere de recorridos por todas las columnas buscando, con distintos cálculos, cuáles de ellas no contienen información.

Hoy te presento, quizá no la conozcas, la función:
Table.Profile(table as table, optional additionalAggregates as nullable list) as table
la función inicialmente está pensada para devolver información estadística sobre el contenido de cada columna de nuestra tabla, tal como cálculos de valores:
-mínimo de la columna
-máximo de la columna
-media
-desviación estándar
-Cuenta de elementos (celdas no vacías)
-Cuenta de vacíos/nulos
-cuenta de elementos distintos

También nos ofrece opcionalmente la posibilidad de crear nuestros propios cálculos (lo que veremos en próximas entregas).

En el ejemplo de hoy nos centraremos en 'algo sencillo' como es eliminar columnas vacías.
Supongamos una fuente de datos como la de la imagen siguiente, sobre la cual queremos operar y 'limpiar'.

Como siempre cargaremos el rango o tabla a nuestro Editor de consultas donde realizaremos la siguiente transformación:

let
    Origen = Excel.CurrentWorkbook(){[Name="ndtest"]}[Content],
    //Obtenemos estadísticas de la tabla
    //pero solo nos quedamos con tres aspectos relevantes:
    //Column: nombre de la columna
    //Count: número de filas de la columna
    //NullCount: número de elementos nulos/vacíos en la columna
    TP = Table.Profile(Origen)[[Column],[Count],[NullCount]],

    //filtramos para obtener únicamente las columnas donde
    //el número de vacíos/nulos es igual al total de elementos
    //i.e., cuando  TODOS son nulos/vacíos
    ColsVacias = Table.SelectRows(TP, each ([NullCount] = [Count]))[Column],

    //eliminamos dichas columnas con todos los elementos nulos
    //es decir, las columnas sin datos
    TablaLimpia=Table.RemoveColumns(Origen,ColsVacias)
in
    TablaLimpia

La simplicidad del proceso es asombrosa... En un par de líneas se ha podido discriminar qué columnas no tienen datos y 'removerlas' empleando Table.RemoveColumns.
Un truco importante es la selección de las columnas que retorna Table.Profile y que vamos a necesitar:
TP = Table.Profile(Origen)[[Column],[Count],[NullCount]],
Si no hicieramos esta selección, por motivos desconocidos por mí, la tabla resultante de Table.Profile no admite que apliquemos 'filtros' sobre sus campos...

La magia de Power Query no deja de sorprender ;-)

Seguir leyendo...

Power Query: Acceso a OneDrive local

En el post previo vimos como conseguir de manera automática nuestro usuario y unidad raiz.
Como continuación hoy veremos cómo acceder, basado en dicha información, al contenido de OndeDrive sincronizado en local.
OJOOO, el acceso a esa copia local puede mostrar datos no actualizados por fallos en el proceso de actualización de OneDrive!!

A partir de la consulta previa, crearemos una nueva Consulta en blanco con el siguiente código:

let
    usuario=NombresUSER[Name]{0},
    rutalocal=NombresUSER[Folder Path]{0},

    //accedemos al contenido de la carpeta de Users
    Origen = Folder.Contents(rutalocal),
    Origen2=Origen, //truco para 'evitar' el paso oculto de Navegación
    //Vamos al contenido de la carpeta con nuestro nombre de usuario
    Nombre = Origen2{[Name=usuario]}[Content],
    //y navegamos por fin a la carpeta de OneDrive
    // OJOOOO, personaliza el nombre de tu carpeta!!!
    OneDrive = Nombre{[Name="OneDrive - excelforo"]}[Content]
in
    OneDrive

El resultado es un listado de todas las carpetas y ficheros contenidos... solo nos queda aplicar algún criterio de búsqueda para acceder a la información deseada...

El código M desarrollado es muy sencillo, basándose en la función Folder.Contents que permite acceder al contenido de la carpeta y navegar por éste... de manera más rápida y ágil que con la función Folder.Files.

Por supuesto, igualmente podremos automatizar el acceso a la ruta Web de OneDrive/SharePoint:

let
    //empleando los datos obtenidos en la consulta previa
    usuario=NombresUSER[Name]{0},
    //componemos nuestra ruta URL
    rutaWeb="https://ircalcerrada-my.sharepoint.com/personal/" & usuario & "_excelforo_com/",

    //https://ircalcerrada-my.sharepoint.com/personal/excelforo_excelforo_com/Documents
    //accedemos al contenido de la carpeta de SharePoint/OneDrive
    Origen = SharePoint.Contents(rutaWeb, [ApiVersion=15]),
    //y a la carpeta que nos interese...
    Origen2=Origen{[Name="Documents"]}[Content]
in
    Origen2

Recuerda habilitar los credenciales de usuario cuando lo solicite ;-)

Seguir leyendo...

Power Query: Nombres de usuario en mi equipo

Antes de que comiences la lectura indicarte que este es la primera parte de un desarrollo que nos permitirá personalizar los accesos al contenido en la nube, como por ejemplo, OneDrive, DropBox, etc... siempre que tengamos dicho contenido sincronizado en local!!.

Todos sufrimos con las rutas absolutas o relativas de nuestro contenido en la nube, e intentamos desarrollar nuestro código para que pueda ser empleado en diferentes máquinas, lo que no siempre es sencillo. (lee algo más aquí)
Bajo la premisa que tenemos sincronizado nuestro contenido en nuestro equipo, en alguna unidad local (probablemente C: aunque no siempre!!) realizaremos un barrido general en busca de nuestro usuario y también de la unidad raiz donde podremos localizar la copia local sincronizada de nuestra 'nube'... lo que servirá y se adaptará en cualquier equipo de trabajo...

Dando por supuesto, quizá algún experto en sistemas pueda corregirme, que las unidades pueden ser nombradas por una sola letra (de la A a la Z), realizaremos un barrido por todas esas unidades buscando una información en concreto...

Veamos cómo.
Abriremos una consulta en blanco dentro de nuestro Editor de Power Query donde escribiremos el siguiente código M:

let
    //de la lista de letras de la A a la Z
    //concatenado con la carpeta \Users\ donde encontraremos todos los usuarios 
    //del equipo
    //verificamos cuáles existen, eliminado el resto....
    Origen=List.RemoveNulls(
            List.Transform({"A".."Z"}, 
                each 
                    let 
                    tbl=Folder.Contents(_ & ":\Users\"),
                    check=if (try tbl{0} otherwise null)<>null then tbl else null
                    in 
                    check)),

    //La lista resultante de unidades existentes con ese Carpeta
    //la convertimos en Tabla
    ConvertidoTablaRecords = Table.FromColumns({Origen},{"Registros"}),
    //y las combinamos en una única Tabla para facilitar el trabajo
    ListaContenidos = Table.Combine(ConvertidoTablaRecords[Registros]),

    Origen2=ListaContenidos,
    //Expandimos la columna de Atributos en busca de ciertas características
    //que hacen únicos a nuestros usuarios
    ExpandeAttributes = Table.ExpandRecordColumn(Origen2, 
            "Attributes", 
            {"Kind", "ReadOnly", "Hidden"}, 
            {"Kind", "ReadOnly", "Hidden"}),
    //filtramos y mostramos dos columnas importantes: Nombre del usuario y unidad de la raiz
    FiltrosTRUE_FALSE = Table.SelectRows(ExpandeAttributes, each ([Kind] = "Folder") and ([Hidden] = false) and ([ReadOnly] = false))[[Name],[Folder Path]]
in
    FiltrosTRUE_FALSE

En mi ejemplo aparecen dos usuarios, con dos unidades raiz distintas (c: y d:) ya que trabajo con dos discos duros (cada uno con un sistema operativo instalado)...
También nos podríamos topar con un serie de diferentes usuarios logados en nuestro equipo...
Los filtros aplicados en el último paso están destinados a discriminar los no deseados, y quedarnos únicamente con el nuestro.

En el siguiente artículo emplearemos el usuario y ruta conseguida para acceder a la copia local de nuestro contenido en OneDrive ;-)

Seguir leyendo...

Power Query: Un Record de 'pasos'

Trabajar con Records en Power Query siempre es interesante y práctico... Te recomiendo leas este artículo de hace un par de años, plenamente vigente: Trabajar con Records.

En ese artículo se describen algunos detalles de los 'Records' (desde los más simples a algunos avanzados), de entre los cuales para el artículo de hoy, me interesa detenerme en qué tipo de datos podemos añadir a un Record:
- valores de texto, fecha, numéricos...
- Listas de todo tipo
- otros Records
- y en especial, también admite Tablas!!

Este es un punto interesante, ya que nos abre un abanico de posibilidades... La idea es trabajar con Tablas como parte de un Record.

Seguramente no te hayas percatado de una opción por defecto cuando cargamos datos de un rango o tabla, donde se gestionan automáticamente esos datos a través de un Record.
Veámoslo con un ejemplo. Partiremos de dos tablas en nuestra hoja de cálculo: TblVENTAS y TblPRECIOS

Cuando realizamos el proceso de carga de datos con cualquiera de las opciones conocidas y entramos en el código generado de la consulta vemos:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblVENTAS"]}[Content]
in
    Origen

Si desglosamos las partes de esa línea tenemos:
1- Excel.CurrentWorkbook(): con el que obtenemos una Tabla con todos los rangos (dinámicos o con nombres definidos) y tablas empleadas.
2- al añadirle la extensión, OJO ahora!!: {[Name="TblVENTAS"]} obtenemos un Record que contiene en uno de sus campos/fields una Tabla con el contenido de ésta!!!.
En este punto indicamos que nos retorne el registro de la tabla anterior que coincida, en la columna 'Name' con el texto 'TblVENTAS' (una 'especie' de filtro...)
3- información que recuperamos fácilmente reclamando o llamando a ese campo:[Content]
Esta llamada muestra el contenido de esa Tabla que existía dentro del campo de nuestro registro...
¡¡Increible lo que ocurre en una sola línea de código!!.

Pero esto no es lo más interesante de los Records...
Imagina que has realizado una transformación de n pasos en tu consulta.. por ejemplo:

let
      Origen = Excel.CurrentWorkbook(){[Name="TblVENTAS"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Fechas", type date}, {"Artículo", type text}, {"Unidades", Int64.Type}}),
    NombreMes = Table.TransformColumns(TipoCambiado, {{"Fechas", each Date.MonthName(_), type text}}),
    Agrupacion = Table.Group(NombreMes, {"Fechas", "Artículo"}, {{"Acum_Uds", each List.Sum([Unidades]), type nullable number}})
    in
    Agrupacion

un ejemplo sencillo de query donde obtenemos los datos de una Tabla de la hoja de cálculo, para luego cambiar los tipos de datos de las diferentes columnas, transformar el campo Fecha para visualizar el nombre del mes, y acabar realizando una doble agrupación por nombre del mes y artículo...
Nada especial... pero fíjate ahora en el cambio que vamos a realizar en el código:

[
      Origen = Excel.CurrentWorkbook(){[Name="TblVENTAS"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Fechas", type date}, {"Artículo", type text}, {"Unidades", Int64.Type}}),
    NombreMes = Table.TransformColumns(TipoCambiado, {{"Fechas", each Date.MonthName(_), type text}}),
    Agrupacion = Table.Group(NombreMes, {"Fechas", "Artículo"}, {{"Acum_Uds", each List.Sum([Unidades]), type nullable number}})
    ]

Eliminamos la expresión let ... in ..., sustituyéndola por los corchetes [...].
Esto convertira nuestro proceso en un registro, donde el contenido de cada paso (sea Lista, Tabla, Registro o Valor), se carga en un campo del recién Record creado:

Tenemos un Record compuesto por distintos campos, uno por cada paso!!. Alucinante!
A partir de aquí podemos trabajar sobre ese Record como con cualquiera creado de la forma tradicional...
Por ejemplo, recuperamos la Tabla del campo 'Agrupacion' (era el último paso de nuestra consulta), para combinarlo con otra Tabla y finalizar expandiendo la info requerida:

let
    //obtenemos el campo 'Agrupacion' del Record 'VENTAS'
    Origen = VENTAS[Agrupacion],

    //para conbinar el contenido de ese campo (una tabla) con una segunda tabla (TblPRECIOS)
    CombinoPRECIOS = Table.NestedJoin(Origen, {"Artículo"}, PRECIOS, {"Pdto"}, "PRECIOS", JoinKind.LeftOuter),
    //y finalizar expandiendo un valor
    ExpandePRECIOS = Table.ExpandTableColumn(CombinoPRECIOS, "PRECIOS", {"Precio"}, {"Precio"})
in
    ExpandePRECIOS

O también podríamos incorporar un nuevo campo al Record generado, por ejemplo, la Tabla final generada en el ejemplo anterior:

let
    //obtenemos el campo 'Agrupacion' del Record 'VENTAS'
    Origen = VENTAS[Agrupacion],

    //para conbinar el contenido de ese campo (una tabla) con una segunda tabla (TblPRECIOS)
    CombinoPRECIOS = Table.NestedJoin(Origen, {"Artículo"}, PRECIOS, {"Pdto"}, "PRECIOS", JoinKind.LeftOuter),
    //y finalizar expandiendo un valor
    ExpandePRECIOS = Table.ExpandTableColumn(CombinoPRECIOS, "PRECIOS", {"Precio"}, {"Precio"}),

    //Incorporamos al REcord VENTAS un nuevo campo que contiene la Tabla previa
    NuevoRecord= VENTAS & [ExpandePRECIOS=ExpandePRECIOS]
in
    NuevoRecord

O un último ejemplo, recuperamos un campo de nuestro Record, para luego seleccionar alguna columna de la Tabla contenida en dicho campo:

let
    //obtenemos el campo 'Agrupacion' del Record 'VENTAS'
    Origen = VENTAS[Agrupacion],

    //para conbinar el contenido de ese campo (una tabla) con una segunda tabla (TblPRECIOS)
    CombinoPRECIOS = Table.NestedJoin(Origen, {"Artículo"}, PRECIOS, {"Pdto"}, "PRECIOS", JoinKind.LeftOuter),
    //y finalizar expandiendo un valor
    ExpandePRECIOS = Table.ExpandTableColumn(CombinoPRECIOS, "PRECIOS", {"Precio"}, {"Precio"}),

    //Incorporamos al REcord VENTAS un nuevo campo que contiene la Tabla previa
    NuevoRecord= VENTAS & [ExpandePRECIOS=ExpandePRECIOS],

    //Del nuevo registro creado, recuperamos uno de sus campos
    //recuerda que el contenido, en este caso, son Tablas
    //Y de esa tabla seleccionamos dos de sus columnas
    Seleccion = NuevoRecord[ExpandePRECIOS][[Artículo],[Precio]]
in
    Seleccion

Y mil posibilidades más...

En definitiva, realizar esta conversión de Query a Record facilita el traspaso de información entre nuestras 'consultas', y sobre todo agiliza el trabajo a la hora de procesar nuestros datos.

Seguir leyendo...

LAMBDA: Completar Fechas

Una situación con la que me encuentro a menudo es trabajar con rangos de datos, basados en rangos temporales, en los que NO existen ciertas fechas, i.e., trabajamos con listados con saltos de fechas.
Esto se convierte en un problema cuando hay que cruzarlos con otros listados en los que si existe la totalidad de las fechas.

Veremos un par de soluciones directas para el caso en que NO existieran fechas repetidas, y solo pretendieramos completar los huecos entre fechas....
Una primera opción se basa en el empleo de la función APILARH y BYROW.
Así en F5 podemos escribir:

=LET(rDatos;$B$5:$D$17;
fechas;INDICE(rDatos;0;1); Pais;INDICE(rDatos;0;2);Importes;INDICE(rDatos;0;3);
desde;MIN(fechas);hasta;MAX(fechas);
rFechasN;SECUENCIA(hasta-desde+1;;desde;1);
rPaisN;BYROW(rFechasN;LAMBDA(vFecha;BUSCARX(vFecha;fechas;Pais;"")));
rImporteN;BYROW(rFechasN;LAMBDA(vFecha;BUSCARX(vFecha;fechas;Importes;"")));
APILARH(rFechasN;rPaisN;rImporteN))

Observa en la imagen anterior cómo se han completado las fechas faltantes, pero como falla al recuperar fechas repetidas...
La lectura de la función es simple, ya que comenzamos declarando nuestras variables de trabajo... que basicamente son el rango completo origen y la definición de las columnas/campos a recuperar.

...
   rDatos;$B$5:$D$17;
fechas;INDICE(rDatos;0;1); Pais;INDICE(rDatos;0;2);Importes;INDICE(rDatos;0;3);
desde;MIN(fechas);hasta;MAX(fechas);
...

También, con SECUENCIA, obtenemos un rango de fechas completo.

...
    rFechasN;SECUENCIA(hasta-desde+1;;desde;1);
    ...

En el siguiente paso realizamos búsquedas de información para cada fecha del rango completado; la función BYROW nos ayudará en esto, recorriendo cada fecha y realizando una búsqueda (con BUSCARX) sobre el rango original.

...
    rPaisN;BYROW(rFechasN;LAMBDA(vFecha;BUSCARX(vFecha;fechas;Pais;"")));
rImporteN;BYROW(rFechasN;LAMBDA(vFecha;BUSCARX(vFecha;fechas;Importes;"")));
    ...

Finalizamos 'juntando' todo con APILARH

...
    APILARH(rFechasN;rPaisN;rImporteN))

Otra opción, de las muchas existentes, sería emplear la función ARCHIVOMAKEARRAY, como podemos ver en J5:

=LET(rDatos;$B$5:$D$17;
fechas;INDICE(rDatos;0;1);
desde;MIN(fechas);hasta;MAX(fechas);
rFechasN;SECUENCIA(hasta-desde+1;;desde;1);
nFilas;FILAS(rFechasN);nCols;COLUMNAS(rDatos);
rDatosN;ARCHIVOMAKEARRAY(nFilas;nCols;LAMBDA(f;c;SI(c>1;BUSCARX(INDICE(rFechasN;f);fechas;INDICE(rDatos;0;c);"");INDICE(rFechasN;f))));
rDatosN)

Con un comienzo muy similar a la fórmula anterior, donde se declaran las variables que vamos a necesitar:

...
   rDatos;$B$5:$D$17;
fechas;INDICE(rDatos;0;1);
desde;MIN(fechas);hasta;MAX(fechas);
rFechasN;SECUENCIA(hasta-desde+1;;desde;1);
nFilas;FILAS(rFechasN);nCols;COLUMNAS(rDatos);
...

La diferencia es que en este caso, para llegar a idéntico resultado, el cálculo de los elementos de nuestra matriz resultante única, se contruye con ARCHIVOMAKEARRAY, finalizando así el proceso...
:

...rDatosN;ARCHIVOMAKEARRAY(nFilas;nCols;LAMBDA(f;c;SI(c>1;BUSCARX(INDICE(rFechasN;f);fechas;INDICE(rDatos;0;c);"");INDICE(rFechasN;f))))

Nuevamente se emplea como función de búsqueda BUSCARX, por lo que el caso de fechas repetidas NO está contemplado...

¿Y entonces cómo solucionamos el caso extremo de tener saltos de fechas y además fechas repetidas??.
Veamos una posible solución en N5 empleando APILARV, FILTRAR o BYROW entre otras:

  =LET(rDatos;$B$5:$D$17;
fechas;INDICE(rDatos;0;1);
desde;MIN(fechas);hasta;MAX(fechas);
rFechasN;SECUENCIA(hasta-desde+1;;desde;1);
rNOCoincidentes;LET(criterio;BYROW(rFechasN;LAMBDA(vFecha;NO(SI.ERROR(COINCIDIR(vFecha;fechas;0)>0;FALSO))));FILTRAR(rFechasN;criterio));
ORDENAR(SI.ERROR(APILARV(rDatos;rNOCoincidentes);"")))

Como en los casos anteriores el inicio de la fórmula es el mismo, declaro variables (básicamente las mismas):

...
   rDatos;$B$5:$D$17;
fechas;INDICE(rDatos;0;1);
desde;MIN(fechas);hasta;MAX(fechas);
rFechasN;SECUENCIA(hasta-desde+1;;desde;1);
...

Para continuar con el cálculo de las 'fechas faltantes':

... rNOCoincidentes;LET(criterio;BYROW(rFechasN;LAMBDA(vFecha;NO(SI.ERROR(COINCIDIR(vFecha;fechas;0)>0;FALSO))));FILTRAR(rFechasN;criterio));...

donde la función FILTRAR nos permite obtener el listado de fechas NO existentes, a partir de un vector de VERDADEROS y FALSOS generado con BYROW.
La fórmula termina aplicando APILARV para unir los datos originales (la totalidad!!) con las fechas que faltaban para completar el rango temporal...

Como siempre abierto a aprender y comentar alguna idea alternativa para dar solución al problema ;-)

Seguir leyendo...

Power Query: Each o Function

Este es un ar´ticulo que seguramente debería haber escrito bastante antes, ya que me parece muy importante para trabajar correctamente con muchas de las funciones M existentes...
Hablaré en esta ocasión de la sintaxis Each _ vs su alternativa Function.

En multitud de ejemplos que hayamos podido trabajar, recurrentemente hemos empleado la sintaxis Each _ que según el contexto de uso podría ser:
- un registro (o fila), si trabajamos en el contexto de una tabla
- un elemento si trabajamos sobre una lista
Esto es, básicamente, Tablas y Listas, serían los dos contextos donde el concepto de Each _, entendido como 'cada elemento de una colección', tiene sentido...
Si bien, en ciertos casos, también se podría aplicar al trabajar sobre las partes de un Record.

Teniendo claro esta idea de lo que implica la sintaxis de 'Each _', debemos saber que Power Query lo lee e interpreta como si fuera una función, es decir, evalúa o transforma cada elemento de esa colección.
De hecho, en la declaración de las funciones M, lo normal es que el argumento requerido exija una función!!.
Y este es el punto importante, puesto que es posible (y recomendable en muchas ocasiones) cambiar la forma de expresar ese recorrido por nuestra colección por la sintaxis de las funciones en M, que en su forma más simple responde a la estructura:
( nombre_variable ) => tratamiento_de nombre_variable
i.e., declaramos el nombre de nuestra variable (usaremos uno que sea descriptivo de su uso) entre paréntesis ,seguido de los signos => y a continuación la transformación o cálculo deseado sobre nuestra variable.
Recuerda que esa variable representará una cosa u otra según el contexto!!.

Veamos algunos ejemplos...
Cargaremos los datos de una sencilla tabla 'Tabla1' con cuatro campos: Cliente, Artículo, Unidades y Precio Unitario

Veamos nuestro código creado en una Consulta en blanco dentro del editor de Power Query:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    //Uso de la sintaxis Each _
    //donde _ corresponde a cada registro de la tabla 'Origen'
    //y como sobre cualquier Record, podemos llamar a cualquiera de sus campos
    Test1=Table.AddColumn(
                Origen,
                "Prueba1", 
                each _[Unidades] * _ [Precio Unitario]),

    //Uso alternativo de Funciones
    //Declaro una variable, con el nombre 'registro'
    //Y para facilitar la lectura empleo let...in..
    //asignando valor a dos elementos 'uds' y 'pz'
    // a partir de la variable anterior
    // para finalmente devolver el producto de ambos
    Test2=Table.AddColumn(
                Test1,
                "Prueba2", 
                (registro)=> 
                    let 
                        uds=registro[Unidades],
                        pz=registro[Precio Unitario]
                    in 
                        uds * pz )

in
    Test2

El resultado, obviamente, es el mismo en los dos casos descritos: Test1 y Test2

Ambas alternativas tienen sus pros y sus contras...
Ventaja de Each: fácil de escribir
Inconveniente de Each: en ocasiones difícil de leer o interpretar

Ventaja de usar funciones: facilita la lectura e interpretación, sobre todo en contextos múltiples.
Inconveniente de las funciones: algo más largo de escribir

Quizá aún no estés convencido de las bondades de emplear la sintaxis de funciones en lugar de Each...
Veamos pues un par de ejemplos más.

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    //Empleando la sintaxis de funciones
    //obtendremos el producto de cada producto por el precio máximo de cualquier venta de nuestra tabla
    Test3=Table.AddColumn(
                Origen,
                "Prueba3", 
                (registro)=> 
                    let 
                        uds=registro[Unidades],
                        pz=List.Max(Origen[Precio Unitario])
                    in 
                        uds * pz ),

    //DOBLE Each_ DOBLE Función
    //En este caso existe un doble recorrido
    //en su parte más profunda recorremos y seleccionamos aquellos registros que cumplan una condición
    //sobre un elemento del recorrido de los registros del bucle principal
    Test4=Table.AddColumn(
                Test3,
                "Prueba4", 
                (registro)=> 
                    let 
                        uds=registro[Unidades],
                        //art=registro[Artículo],
                        pz=List.Max(Table.SelectRows(
                                        Test3, 
                                        (seleccion)=> seleccion[Artículo]=registro[Artículo])[Precio Unitario])
                                        //(seleccion)=> seleccion[Artículo]=art)[Precio Unitario])
                    in 
                        uds * pz )
in
    Test4

Fíjate en estos ejemplos, donde empleando las funciones y declarando nuestras variables, se clarifica qué estamos haciendo... además, si nos apoyamos en la estructura let..in..., podremos construir situaciones más complejas.

Como ejemplo diferente tenemos el 'Test4', donde recorremos cada fila de nuestra tabla, recuperando el valor de cada 'artículo' y 'número de unidades', y para recuperar el 'precio' optamos por un nuevo proceso de iteración por la tabla para obtener el mayor de los precios solo de aquellos registros coincidentes con el del registro principal actual!!.

En el código descrito he dejado comentado una forma alternativa (más clara a mi forma de ver) que requiere de una línea más de código.

Espero haber aclarado algunas ideas a este respecto y te haya sido de utilidad.

Seguir leyendo...

Power Query: Operador de fusión -??-(COALESCE)

Seguramente estés acostumbrado a 'luchar' contra los valores null empleado sentencias condicionales de control (if... then...else...).
Sin duda un método sencillo y fácil de gestionar... Pero, ¿conoces el operador de fusión '??' ?.
Este operador devuelve el resultado de su operando izquierdo si no es Null; en caso contrario, tednremos como resultado el valor de su operando derecho.
Esto es, el operando derecho solo se evalúa si el operando izquierdo es NULL.

En otros lenguajes este operador-función es conocida como COLAESCE, que retorna a la primera expresión no nula entre sus argumentos.

Veamos algunos ejemplos de uso...
Supongamos que partimos de una tabla como la siguiente:

La idea es calcular el cociente entre 'Habitantes' y 'Km2' para conocer la densidad de población (Nota: Son datos aleatorios!!!)

La forma clásica sería agregar una nueva columna condicional para evitar el nulo en los casos de no disponer valores sería la 'Opción1', comparada con otra empleando '??':

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],

    //Opción 1
    //Doble condicional para controlar los nulos en las columnas implicadas
    CondicionalEstandar=Table.AddColumn(
                    Origen,
                    "Densidad1", 
                    each if [Km2]=null then 0 else 
                        if [Habitantes]=null then 0 else [Habitantes]/[Km2]),
    
    //Opción 2
    //Usando el operador de fusión para en caso de devolver nulo
    //cambiarlo por 0
    Coalesce=Table.AddColumn(
                CondicionalEstandar,
                "Densidad2",
                each [Habitantes]/[Km2]??0 )
in
    Coalesce

Vemos que el resultado es equivalente... aunque más directo a la hora de escribir el código.

Otro ejemplo, donde tenemos dos columnas con valores.. y en algunos registros falta información.
Un código alternativo:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],

    CondicionalEstandar = Table.AddColumn(
                Origen, 
                "UltimoDatoPIB_2021_2022_v1", 
                each if [PIB_2022]<>null then [PIB_2022] else 
                    if [PIB_2021]<>null then [PIB_2021] else 0),

     Coalesce=Table.AddColumn(
                CondicionalEstandar,
                "UltimoDatoPIB_2021_2022_v2",
                each [PIB_2022]??[PIB_2021]??0)

in
    Coalesce

Ejemplo donde se ha aplicado un doble operador de fusión -'??'-, dando doble solución al problema.
La lectura de
[PIB_2022]??[PIB_2021]??0
sería:
Nos quedamos con el dato de [PIB_2022] si es NO nulo; si lo fuera ,evaluaríamos [PIB_2021] con igual criterio, en caso de que [PIB_2021] fuera NO nulo nos quedaríamos con este valor... y si no lo fuera retornaríamos el valor dado 'cero'.

La simpleza y elegancia de este operador no debería pasar desapercibida ;-)

Seguir leyendo...

Power Query: Alternativas a SelectColumns

La función M que nos permite eleccionar columnas de una tabla, ordenar dichas columnas y gestionar su existencia o no (Table.SelectColumns) es sin duda una función impresionante, por su versatilidad, y sobre todo fácil de emplear.
Puedes leer un ejemplo de la función...

Hoy veremos, sin embargo, una alternativa interesante trabajando sobre los códigos de las columnas empleando los 'corchetes' para ello. Hablamos de la expresión de acceso a campos, o en su caso de la proyección de campos.
Alternativa que nos permitirá seleccionar u ordenar columnas!!

Para explicar la casuística cargaremos una tabla con cuatro columnas: País, Región, Ciudad, Habitantes

Todos conocemos la sintaxis con corchetes simples para referirnos a una columna concreta, y que nos retorna los elementos de la columna en forma de Lista.
Por ejemplo, si escribimos:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
    //elementos de la columna REgión en forma de lista
    ListaColumna=Origen[Región]
in
    ListaColumna

Nada novedoso.. pero hay un par de curiosidades que quizás desconozcas.

Un primer aspecto interesante es que si doblamos los corchetes lo que obtenemos no es una lista sino una tabla con esa columna indicada...

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
    //elementos de la columna REgión en forma de tabla
    //OJO con el doble corchete
    TablaColumna=Origen[[Región]]
in
    TablaColumna

Este resultado obtenido da pie a otro aspecto y uso relevante de esta forma de trabajar, ya que nos permite recuperar tantas columnas como necesitemos y en el orden que requiramos!! Por ejemplo para recuperar una nueva tabla con las columnas: Ciudad y Habitantes.

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
    //elementos de la columna REgión en forma de tabla
    //OJO con el doble corchete
    TablaColumna=Origen[[Ciudad],[Habitantes]]
in
    TablaColumna

No está mal... pero hay más, si necesitaras optimizar tu selección de columnas, aún desconociendo su existencia, podrías aplicar el operador de opcionalidad (?). Por ejemplo, reclamamos una columna que puede o no existir ('Provincia'):

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
    //elementos de la columna REgión en forma de tabla
    //OJO con el doble corchete
    TablaColumna=Origen[[Ciudad],[Habitantes],[Provincia]]?
in
    TablaColumna

Notemos que al no existir dicha columna 'Provincia' nos retorna valor nulo (efecto del operador de opcionalidad).

Quizá ya te hayas fijado que el orden de aparición de las columnas en la tabla resultante es el que dispongamos.

Sin duda una alternativa a tener siempre en cuenta, ya que nos permite pasar de listas a tablas y viceversa de una manera muy simple (entre otras cosas) ;-)

Seguir leyendo...

Power Query: Table.TransformRows

Si en la (entrada anterior hablábamos de una función alternativa muy interesante como Table.TransformColumns, hoy hablaremos de otra semejante, pero más impresionante si cabe:
Table.TransformRows(table as table, transform as function) as list
Función que crea un elemento list aplicando la operación transform a cada fila de table.

Esta función nos va a permitir, fácilmente, controlar qué columnas queremos elegir (en lugar de Table.SelectColumns), y a un mismo tiempo, incluso podemos realizar un proceso de transformación de nuestros datos!!.

Personalmente no había analizado esta función en profundidad, y me ha sorprendido gratamente ;-)

Iniciemos nuestro ejercicio desde una tabla con cuatro columnas: Fechas, Artículos, Unidades y Name

Objetivo a cumplir:
1-elegir solo las columnas de Fechas, Unidades y País
2-transformar el campo Fechas al formato Date
transformar la Unidades en el doble
transformar el País y quitar el prefijo 'Tbl'
3-Renombrar los nombres de las columnas

Veamos nuestro código creado en una Consulta en blanco dentro del editor de Power Query:

let
    Origen = AlternativaClasica,

    //TransformRows nos permite Elegir Columnas, renombrarlas, y a un tiempo aplicar algún tipo de transformación!!
    Transformacion=Table.TransformRows(Origen, 
            each [Fechas=Date.From([Fechas]),   //convertimos las FEchas 
                B = [Unidades]*2,               //doblamos las Unidades
                País=Text.End([Name],2)] ),     //Eliminamos el prefijo de cada texto

    //convertimos la lista de registros que no retorna TransformRows en Tabla
    ATabla=Table.FromRecords(Transformacion)
in
    ATabla

El resultado es el esperado...

Función muy versatil, sin duda.

Seguir leyendo...

Power Query: Table.TransformColumns

Habitualmente, a la hora de trabajar con columnas que contienen libros binarios, listas o sencillamente Tablas, optamos por trabajar agregando una columna nueva, y realizar la transformación en el contexto de la función Table.AddColumn.
Nos acostumbramos a emplear técnicas que nos resultan cómodas...

Sin embargo, tenemos alternativas igual de interesantes, como Table.TransformColumns... incluso más, ya que no requiere agregar nuevas columnas.

La función Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as table
la cual transforma table mediante la aplicación de cada operación de columna enumerada en transformOperations (donde el formato es { nombre de la columna, transformación } o { nombre de columna, transformación, nuevo tipo de columna }). .

Planteemos el siguiente ejercicio. Tenemos tres tablas en nuestro fichero, una tabla por país.
Nuestro objetivo será consolidar/anexar dichas tablas en una hoja nueva... añadiendo una columna extra con el nombre de la tabla correspondiente.

Veamos nuestro código creado en una Consulta en blanco dentro del editor de Power Query.

let
    //recuperamos todas las tablas del libro actual que empiecen con 'Tbl"
    Origen = Table.SelectRows( Excel.CurrentWorkbook(), each Text.StartsWith([Name],"Tbl")),

    //aplicamos la trasnformación a la columna de Contenido 'Content'
    Transformacion= Table.TransformColumns(Origen,
            {{"Content", each
                let
                    pos= List.PositionOf(Origen[Content], _),   //identifico el número de fila
                    nombre=Origen[Name]{pos},                   //recupero el Nombre desde el campo 'Name'
                    ordeno=Table.Sort(_, {"Fechas"}),           //ordeno cada tabla por Fechas
                    //y añado a cada tabla un campo nuevo con el nombre anterior
                    AddCol=Table.AddColumn(ordeno, "País", each nombre)
                in
                    AddCol }}),

    //acabamos Combinando (anexando) las distintas tablas de la columna 'Content'
    Consolidado=Table.Combine(Transformacion[Content])
in
    Consolidado

TRUCO. En lugar de añadir una columna de Índice para determinar posiciones de cada fila, empleamos List.PositionOf para buscar cada elemento recorrido en la columna sobre esa columna....

Observa como la expresión each nos permite recorrer la/s columna/s que indiquemos, aplicándoles algún tipo de acción. En nuestro ejemplo, el proceso de transformación consiste en ordenar cada tabla y además incoporarle una nueva columna.

Para los amantes de lo simple, siempre es posible utilizar el método clásico de expandir columna.

let
    Origen = Table.SelectRows( Excel.CurrentWorkbook(), each Text.StartsWith([Name],"Tbl")),
    ExpandeContent = Table.ExpandTableColumn(Origen, "Content", 
                        {"Fechas", "Artículos", "Unidades"}, 
                        {"Fechas", "Artículos", "Unidades"})
in
    ExpandeContent

Mucho más sencillo... pero limitado por la definición de nombres de columnas.
Que en todo caso no hay que despreciar, al contario... en muchos casos, con un volumen alto de datos, la rapidez de ejecución es un punto a su favor.

Seguir leyendo...

LAMBDA: Cálculo desviaciones

Hoy toca algo de formulación específica para el cálculo de desviaciones, pero solo para los meses consecutivos.
Se trata a partir de un listado de fechas, llegar al porcentaje de desviación de los importes acumulados por mes, solo en los casos de meses correlativos.

Algo muy concreto pero interesante por un par de técnicas de construcción de fórmulas complejas.

¿Qué vemos en la imagen anterior?
Cómo, con una única fórmula, se ha conseguido procesar los datos listados para internamente:
primero - acumular por mes los importes correspondientes.
segundo - calcular las desviaciones sobre los importes acumulados... si cumplen la condición de ser consecutivos. tercero - retornar únicamente los meses en texto y las desviaciones calculadas.

Veamos la 'formulita' en cuestión:

=LET(datos;$B$3:$D$19;
    datos_ordenados; ORDENAR(datos; 1; 1);
    fechas;INDICE(datos_ordenados;0;1);
    importes;INDICE(datos_ordenados;0;3);
    convertido;UNICOS(MES(fechas));
    acumulado; MAP(SECUENCIA(FILAS(convertido));
    LAMBDA(fila;  SUMAPRODUCTO(importes*( MES(fechas)=INDICE(convertido;fila;1)))));
    rdo;APILARH(convertido;acumulado);
    
    variacion; MAP(SECUENCIA(FILAS(rdo)); LAMBDA(fila2;
    SI( Y(INDICE(rdo; fila2; 1)=INDICE(rdo; fila2-1; 1)+1);
    (INDICE(rdo; fila2; 2) / INDICE(rdo; fila2-1;2)  )- 1;         "")  )  );
    APILARH(TEXTO(FECHA(2022;EXCLUIR(rdo;;-1);1);"mmm");variacion) )

El proceso es simple...
Comenzamos, con la función LET, declarando y definiendo las variables/rangos sobre los que vamos a trabajar:

=LET(datos;$B$3:$D$19;
    datos_ordenados; ORDENAR(datos; 1; 1);
    fechas;INDICE(datos_ordenados;0;1);
    importes;INDICE(datos_ordenados;0;3);
    convertido;UNICOS(MES(fechas));...

Continuamos procesando los rangos anteriores con la función MAP, la que nos permitirá obtener los importes acumulados por cada mes de nuestro listado.

...
    acumulado; MAP(SECUENCIA(FILAS(convertido));
    LAMBDA(fila;  SUMAPRODUCTO(importes*( MES(fechas)=INDICE(convertido;fila;1)))));
    ...

Seguidamente compondremos una matriz de resultados con APILARH, uniendo el listado numérico de meses al recién calculado vector de importes acumulados.

...
    rdo;APILARH(convertido;acumulado);
    ...

Sobre esta matriz resultante, aplicaremos un nuevo proceso, evaluando la condición sobre el listado de meses ordenado de la primera columna de la matriz anterior, y solo en el caso de que sean meses consecutivos, i.e., que el mes previo sea igual al actual mas uno, aplicar el cálculo clásico de desviaciones mes_presente / mes_anterior -1

...
    variacion; MAP(SECUENCIA(FILAS(rdo)); LAMBDA(fila2;
    SI( Y(INDICE(rdo; fila2; 1)=INDICE(rdo; fila2-1; 1)+1);
    (INDICE(rdo; fila2; 2) / INDICE(rdo; fila2-1;2)  )- 1;         "")  )  );
    ...

Concluimos nuestra fórmula juntando, otra vez con APILARH los meses ya convertidos a TEXTO, junto a los porcentajes de desviación obtenido.

...
    APILARH(TEXTO(FECHA(2022;EXCLUIR(rdo;;-1);1);"mmm");variacion)
    ...

Tenemos pues las pistas para obtener cálculos complejos.
1-declarar variables
2-realizar cálculos intermedios
3-concluir el cálculo con los valores anteriores.
Espero te haya resultado interesante... y quedo abierto a cualquier sugerencia ;-)

Seguir leyendo...