jueves, 24 de septiembre de 2020

Power Query: #shared-Listado de funciones M

Algún buen amigo me ha 'reprochado' que mis explicaciones, en bastantes ocasiones son de temas demasiados complejos, y que no suelo escribir sobre 'asuntos más normales'... No es que esté del todo de acuerdo, pero para sentirme tranquilo, hoy explciaré un buen truco para acceder a la biblioteca de funciones M dentro del editor de Power Query.
Supongamos que solo me interesa llegar a ese listado... así pues accedo, dentro de Excel, a la ficha Datos > grupo Obtener y transformar > desplegable Obtener datos > Desde otras fuentes > Consulta en blanco
Power Query: #shared-Listado de funciones M

Esto abrirá el editor de Power Query con una consulta vacía, sin pasos...
En la barra de fórmulas de la consulta escribiremos:
= #shared
Power Query: #shared-Listado de funciones M

Tras presionar Enter veremos el resultado de la consulta creada, con un listado de todas las funciones M disponibles en Power Query...
Power Query: #shared-Listado de funciones M
Con un funcionalidad adicional, en el listado de funciones tenemos dos columnas, siendo la segunda ('Record') un link a una ventana de ayuda de esa función!!
Power Query: #shared-Listado de funciones M

Basta hacer clic sobre cualquiera de ellas para que se abra una ventana con una breve descripción de esa función, ejemplos, etc...
OJO, por que añade a nuestra consulta un paso con esa función elegida...

De igual forma, podemos realizar búsquedas aplicando filtros sobre la primera columna, buscando por el nombre de la función

Un buen truco, y rápido, si no tuvieramos acceso a internet en ese momento ;-)

martes, 22 de septiembre de 2020

Power Query: El tamaño de los ficheros importa

Toca en el día de hoy analizar el impacto de trabajar con Power Query sobre el tamaño de nuestros ficheros (y su consecuente ralentización).
Partiremos de un fichero .csv con datos inventados de diferentes países por meses, con un total de 380.160 registros.
El peso inicial de este archivo .csv: 29.442 Kb.

Así pues, nuestro primer paso será abrir un libro de Excel (.xlsx) y cargar los datos a una consulta de Power Query.
Desde la Ficha Datos > grupo Obtener y transformar > Obtener datos > Desde un archivo > Desde texto/csv
Power Query: El tamaño de los ficheros importa

Indicaremos la ruta de nuestro fichero .csv y en un primer término simplemente Cargaremos y cerraremos en una Tabla
Power Query: El tamaño de los ficheros importa

Es decir, en este primer experimento no hay tratamiento o transformación de datos.
Tras esperar a la actualización y comprobar que se han cargado las 380.160 filas, procedemos a guardar nuestro libro y comprobar el peso de este... que es de 29.512 Kb.
Unos bytes por encima del peso original del fichero .csv.
Poco esperanzador :-(

Vamos a por una alternativa... Cargaremos y cerraremos en modo Tabla dinámica
Power Query: El tamaño de los ficheros importa

Esperamos a que la consulta actualice sus 380.160 filas y guardamos el fichero.
Ahora su tamaño ha bajado a 10.613 Kb (más o menos un tercio del tamaño original!!) :O
En este caso, al devolver la info a través de un informe de tabla dinámica, y por tanto, no tener la info 'repetida' en la vista de hoja de cálulo, el peso del fichero es sustancialmente menor.

Otra variante asombrosa sería Cargar y cerrar en Solo conexión...
Dejando en esta situación, solo conexión, un peso del fichero de 16 Kb !!!!.
Disponible para combinar con otras posibles consultas si fuera necesario.

En estos casos descritos no ha habido transformación alguna de columnas o filas.. ¿pero que pasaría si Quitamos columnas innecesarias de nuestra consulta??.
Veamos que ocurre si eliminamos algunas columnas (hasta 9 de las 15 existentes en el .csv original).
Tras eliminar dichas columnas volvemos a Cargar y cerrar en modo Tabla, y verificar que se han actualizado las 380.160 filas, guardamos de nuevo el libro de trabajo y comprobamos que su peso es de 10.430 kB
Recuerda que el peso del fichero, con todas sus columnas y filas cargadas y mostradas en una Tabla (primera prueba realizada) era de 29.512 Kb; es decir, eliminando columnas reducimos peso... en mi ejemplo de 29.512 a 10.430.

Si en lugar de cargar y cerrar en una tabla, lo mostramos en un informe de tabla dinámica con las columnas ya eliminadas, comprobaremos que el peso queda en unos 3.827 Kb.
Con todas sus columnas, el equivalente, tenía 10.613 kb. Una bajada de +/- 7 Mb

Continuamos ahora rebajando el número de filas aplicando algún filtro sobre un campo de años
Asi pues, con todas sus columnas de origen y mostrada la consulta sobre una Tabla en la hoja de cálculo, aplicamos un criterio sobre la columna de año para mostrar solo años posteriores a 1975 (los datos originales eran de 1900 a 2019).
Se nos quedan por tanto las 15 columnas originales pero solo con 142.560 filas...
y un tamaño del fichero (frente a los 29.512 Kb) de 11.046 Kb... de nuevo casi un tercio.
Si quitamos columnas, además del filtro de años aplicado, el peso sería de 3.969 Kb.

Puedes comprobar cuantas combinaciones se te ocurran... quitar columnas o aplicar filtros mostrando los datos como Tabla, Informe de tabla dinámica o Solo conexión, pero la conclusión será siempre la misma:
Lo primero que debemos hacer con nuestras consultas es eliminar columnas innecesarias y aplicar los filtros oportunos que muestren información realmente relevante.
Solo una vez hecho esto, podremos decidir en qué forma (Tabla, Tabla dinámica o Conexión solo) mostrar dicha información... según nuestras necesidades.

Una última variable, nada despreciable, aparece con Power Pivot y su Modelo de datos.
Cuando nuestra meta es claramente trabajar con un informe de tablas dinámicas, lo recomendable será realizar todo tipos de cálculos, modificaciones, transformaciones (como las ya comentadas, entre otras) con el Editor de Power Query, para una vez concluido el proceso Cargar y cerrar como Solo conexión pero Agregándolo al modelo de datos
Power Query: El tamaño de los ficheros importa

Posteriormente deberemos entrar en el Administrador de Power Pivot e insertar una Tabla dinámica desde el modelo de datos...
Esto reducirá aún más el peso del fichero, y además agilizará los procesos de cálculo de nuestro libro.

Otras recomendaciones variadas... (adicionales a las anteriores):
- Si trabajas con el Modelo de datos de Power Pivot crea Medidas mejor que Columnas calculadas
- Siempre mejor, si es inevitable, calcular columnas en Power Query que en Power Pivot
/ - Mejor Campo calculado en la tabla dinámica que como Medida en el modelo
- Si existen columnas del tipo DateTime mejor separalas en dos columnas, una solo Date, otra con Time
- Cuando sea posible redondea las columnas (evita decimales 'interminables')
Todos estos puntos, y alguno más, facilitarán que tus procesos sobre grandes bases de datos sean agiles y rápidos en sus cálculos...
Espero algo de lo expuesto te sea de utilidad...

jueves, 17 de septiembre de 2020

VBA: el cuadrado mágico de Leonhard Euler

Hoy toca un poco de programación en VBA para Excel.
Aprovechando un tema siempre interesante: el cuadrado mágico de Euler que reune dos temas apasionantes.. secuencias matemáticas y ajedrez, veremos algunas rutinas de macros interesantes.

Gráfica de un polígono de hasta 20 lados.


En qué consiste este cuadrado 'semimágico' de Euler...
/ Este gran matemático (el del número e) fue capaz de montar la secuencia natural de números del 1 al 64 sobre un tablera de ajedrez (8 x 8 = 64 escaques) distribuidos siguiendo la secuencia de movimientos de un caballo (la figura del ajedrez)!!!.
No solo eso, además cada fila y columna de sus secuencia distribuida sumarán siempre 260... pero encima, si dividimos en cuatro cuadrados de 4 x 4 nuestro tablero, cada parte suma igualmente una misma cantidad: 520 (si, el doble de 260)... increible!!.
Solo le falto que las diagonales también sumaran esos 260 para conseguir el perfecto cuadrado mágico... no se puede tener todo, verdad?.

Los valores de este cuadrado:
VBA: el cuadrado mágico de Leonhard Euler
Nuestra experiencia de hoy será replicar el camino que seguiría ese caballo por el tablero, siguiendo el orden natural 1,2,3,4,..., 64. Mostrando con flechas y colores esos movimientos.
Para ello emplearemos un truco que simula una matriz de constantes con los ocho movimientos posibles de un caballo; y por otro lado usaremos el método Shapes.Addline para añadir con flechas dichos movimientos...
Insertamos un módulo en nuestro proyecto, y en el añadimos nuestro procedimiento auxiliar que permite añadir las flechas:

Sub AñadimosFlecha(FromCell As Range, ToCell As Range)

iniX = FromCell.Left + (FromCell.Width / 2)
iniY = FromCell.Top + (FromCell.Height / 2)
finX = (ToCell.Left + (ToCell.Width / 2))
finY = (ToCell.Top + (ToCell.Height / 2))

'expresión. AddLine (BeginX, BeginY, EndX, Endy)
Set flecha = FromCell.Parent.Shapes.AddLine(iniX, iniY, finX, finY)

'seleccionamos la linea
flecha.Select
'y añadimos la terminación en forma de flecha
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle

End Sub

Por otra parte, en el mismo módulo, añadimos el procedimiento Sub principal:

Sub Euler()
'dejamos celdas sin color
Range("ndEuler").Interior.Color = xlNone
'y eliiminamos las formas...
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
   shp.Delete
Next shp

'limites filas y columnas del cuadro
fila1 = Range("ndEuler").Row
fila8 = fila1 + 8 - 1
col1 = Range("ndEuler").Column
col8 = col1 + 8 - 1

'situación inicio
Range("ndEuler").Cells(1, 1).Select
Selection.Interior.Color = vbCyan
Application.Wait (Now + TimeValue("0:00:02") / 3)
Selection.Interior.Color = vbYellow

ValorActual = Selection.Value

'nos movemos, y probamos encontrar el valor siguiente al Actual
'con el movimiento del caballo!!

'definimos la matriz de constantes de ocho movimientos del caballo posibles...
arrMovs = [{1, -1, 2, 2, 1, -1, -2, -2; 2, 2, 1, -1, -2, -2, 1, -1}]

Dim horizontal As Integer, vertical As Integer, x As Integer, i As Integer
'forzamos los 64 escaques posibles
For i = 1 To 64
    For x = 1 To UBound(arrMovs, 2)
        fA = Selection.Row
        cA = Selection.Column
        fB = arrMovs(1, x)
        cB = arrMovs(2, x)
        
        'control dentro cuadro
        If fA + fB >= fila1 And fA + fB <= fila8 And _
            cA + cB >= col1 And cA + cB <= col8 Then
            'si el movimiento cae dentro del cuador
            If Selection.Value + 1 = Selection.Offset(fB, cB).Value Then
            'y además el valor encontrado es el siguiente...
                'y añadimos los conectores
                Call AñadimosFlecha(Cells(fA, cA), Cells(fA, cA).Offset(fB, cB))
                Cells(fA, cA).Select
                
                'marcamos la celda final
                Selection.Offset(fB, cB).Select
                Selection.Interior.Color = vbCyan
                'damos un retraso de tiempo para resaltar el cambio
                Application.Wait (Now + TimeValue("0:00:02") / 3)
                Selection.Interior.Color = vbYellow
                
                'seleccionamos celda última con valor
                Cells(fA, cA).Offset(fB, cB).Select
            End If
        End If
    Next x
Next i

End Sub

Notemos que previamente he asignado al cuadrado o rango de celdas B2:I9 el nombre definido: 'ndEuler'.

De especial interes cómo hemos definido la matriz de movimientos:
'definimos la matriz de constantes de ocho movimientos del caballo posibles... arrMovs = [{1, -1, 2, 2, 1, -1, -2, -2; 2, 2, 1, -1, -2, -2, 1, -1}]
con dos filas y ocho columnas, desde la que posteriormente poder recuperar sus pares de valores con la notación:
fB = arrMovs(1, x)
cB = arrMovs(2, x)
El resto del código no parece tener especial dificultad, ya que solo son bucles y condicionales que permiten seleccionar las celdas e ir coloreándolas...
De otra parte, para insertar las líneas, empleamos el método:
'expresión. AddLine (BeginX, BeginY, EndX, Endy)
donde definimos el inicio y fin con las propiedades .Top, .Left, .Width y .Height de Range.
El resultado se puede ver, en el video al inicio del post.
Espero te haya resultado interesante ;-)