jueves, 26 de enero de 2023

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'.
Power Query: Table.Profile para eliminar columnas vacías

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

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


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 ;-)

martes, 24 de enero de 2023

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

Power Query: Acceso a OneDrive local

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

Power Query: Acceso a OneDrive local
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 ;-)

jueves, 19 de enero de 2023

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...
Power Query: Nombres de usuario en mi equipo

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

Power Query: Nombres de usuario en mi equipo

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 ;-)

martes, 17 de enero de 2023

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
Power Query: Un Record de 'pasos'

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:
Power Query: Un Record de 'pasos'


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.

jueves, 12 de enero de 2023

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.
LAMBDA: Completar 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 ;-)