Cambio de fichero cargado mediante lenguaje M en Microsoft Power BI

Ayer veíamos la instrucción para cargar un fichero CSV con lenguaje M en Power BI.

En caso de tener que aplicar el mismo proceso a un fichero con un nombre distinto o ubicado en otra carpeta, es posible modificar la carga del fichero con solo modificar la instrucción de carga del fichero.

Para ellos solo es preciso modificar la ruta del fichero, el resto de los cambios se aplicaran sobre los datos del nuevo fichero.

En este caso solo hemos modificado el texto resaltado en rojo.

Fichero original (todo.csv):

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])

Fichero secundario (todoModificado.csv):

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todoModificado.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])bi

El proceso es sencillo.

Primero abrimos el archivo de Power BI.

Luego debemos editar la consulta desde el menú contextual del botón derecho.

La instrucción que se nos muestra se corresponde al paso que tengamos seleccionado en Power BI, que por defecto, es el último realizado.

Seleccionamos el primer paso, para que se nos muestre la instrucción en lenguaje M correspondiente a la carga del fichero CSV.

Antes de la modificación vemos los datos del primer CSV.

Modificamos el nombre del fichero CSV e, importante, pulsamos la tecla intro, para aplicar el cambio.

Inmediatamente se cargan los.datos del nuevo fichero.

Es un truco útil cuando se pretende aprovechar parte de los pasos de un fichero.

Carga csv en power query para Microsoft Excel y Power Bi

Si una herramienta es interesante en el mundillo del análisis de datos esta es Power Query y su lenguaje M.

Esto se debe principalmente a que es una herramienta común tanto a Microsoft Excel como a Power BI.

Hoy os traigo la instrucción que generan Excel y Power BI para cargar el mismo fichero CSV.

Microsoft Excel:

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";",Encoding=1252])

Power BI:

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])

Como podéis ver las diferencias son mínimas, tanto que cualquiera de los dos códigos funcionaría sin problema en la otra herramienta.

En Excel se hizo la importación desde "Obtener y transformar".

La fórmula se encuentra en el primer paso.

Y es exactamente la que os mostraba.

En Power BI, cargamos el fichero CSV desde "Obtener datos".

En la siguiente ventana pulsamos directamente en el botón "Cargar"

Y vemos que, también en el primer paso, se muestra la misma fórmula en lenguaje M.

En este caso la única diferencia, es que Power BI, tiene resaltado de color, como podéis ver en la captura.

ELT vs ETL en Microsoft Excel

En informática a la hora de cargar y tratar datos existen dos conceptos ETL y ELT.

ETL (Extract, Transform, Load)

ELT (Extract. Load, Transform)

En Microsoft Excel, lo más habitual es cargar todos los datos y luego filtrar los datos que se desean, lo que corresponde al proceso ELT (Extract. Load, Transform).

Para ello los cargamos desde "Obtener datos externos"

Este proceso tiene como inconveniente que se cargan datos que no interesan en el análisis, y que luego hay que filtrar o si se realizan cálculos, tenerlos en cuenta para no incluirlos en los cálculos.

Esto aumenta el tiempo de cálculo y reduce el número de filas de interés que podemos tratar.

En el peor de los casos, no podremos cargar todos los datos, ya que también se tendrán en cuenta datos que no interesan, pero que cuentan entre el millón de líneas que se pueden cargar en Microsoft Excel.

También se puede aplicar el método ETL (Extract, Transform, Load), mediante el uso del componente Power Query, que nos permite aplicar filtros y transformaciones a los datos antes de cargarlos.

Para ello se cargan los datos desde el grupo "Obtener y transformar".

Usando este método podremos aplicar las transformaciones a los datos originales, e importar un máximo de un millón de filas de datos de interés, tras haber eliminado los datos no necearios.

El método a utilizar dependerá de la cantidad de elementos del conjunto de dato original, y de lo importante que sea el tiempo de proceso de las fórmulas de la hoja de cálculo.

Informes en Solver

Cuando se usa la herramienta Solver de Microsoft Excel, una de las cosas que se puede hechar de menos es entender que cálculos ha realizado la herramienta.

Si Solver para Microsoft Excel encuentra una solución, o si se para el proceso mediante la tecla ESC, es posible seleccionar unos informes.

Se pueden seleccionar, dependiendo del método de resolución, dos informes.

Reponder o Población.

El de población tiene el siguiente aspecto.

Mientras que el de respuestas tiene el que se muestra a continuación. 

El informe de respuestas está disponible para todos los métodos de cálculo.

Factura de un importe exacto con solver en Microsoft Excel

Un  caso que me han pedido resolver hace tiempo con Microsoft Excel, es a partir de un listado de productos conseguir generar un lote por un importe exacto.

En el ejemplo, tenemos que generar un lote de 500,00 € en el que es obligatorio que se incluya al menos un ejemplar de cada uno de los diez primeros artículos.

La cantidad máxima de ejemplares por título es de 2 ejemplares.

En el video podéis ver el proceso completo.

En la columna C hemos indicado el número mínimo de ejemplares exigido.

En la columna E, será donde solver introduzca datos de partida.

Y en la columna D, sumaremos las dos columnas anteriores, obteniendo el total de ejemplares a incluir en el listado.

La columna F calculará el importe por línea, que se acumulará en la celda I1.

Con los datos de partida, el listado asciente a 169,54 €, ahora necesitamos seleccionar artículos por el resto del importe.

La configuración de Solver es la que se muestra, indicando el valor exacto para la celda I1, cambiando las celdas de la columna E, a la que también aplicaremos como restricción que los valores sean enteros.

La columna D tendrá dos restricciones, mayor o igual que cero y menor o igual que el número de artículos máximo permitido de la misma referencia.

Para este ejemplo el mejor método de resolución es Simplex LP.

Encontrar un valor aproximado, es relativamente sencillo hacerlo a mano, pero encontrar el valor exacto es prácticamente imposible sin usar Solver.

Depósito prismático con la menor cantidad de material calculado mediante Solver

En este caso calcularemos un depósito prismático, con lados rectangurales.

Los criterios son tres:

1 - Una capacidad mínima de 1000 litros.

2 - El uso de la mínima cantidad de material

3 - Para facilitar las mediciones trabajaremos en centímetros

En el vídeo de Youtube podéis ver el proceso completo.

En este caso, partiendo de las dimensiones de Alto, Ancho y Fondo, calculamos, tanto el volumen del depósito, como la superficie, lo que nos dará la cantidad de material necesario.

En este caso vamos a intentar crear el depósito que menos material precise para su construcción.

La fórmulas usadas son la siguientes:

En solver, indicamos que deseamos que la casilla B9, tenga el valor mínimo, esta casilla muestra el total de material necesario.

Y en el campo "cambiando las celadas de variables", seleccionaremos las casillas correspondiente a las medida de Alto, Ancho y Fondo.

En cuanto a las restricciones, indicaremos que el volumen sea mayor o igual a 1000.

En esta ocasión no indicaremos que deseamos el menor posible, porque al usar la mínima cantidad de material, también obtendremos el mínimo volumen.

La otra restricción hará que las medidas Alto, Ancho y Fondo sean en valores enteros.

La configuración de solver queda como se muestra.

En un primer intento vamos a lanzar solver con el método "Siplex LP"

Vemos que en esta ocasión, solver nos indica que no es posible encontrar la solución con este método porque no se cumplen las condiciones de linealidad.

Cambiamos el método de resolución a "GRG Nonlinear" y ejecutamos.

En este caso si que obtenemos un resultado.

El resultado óptimo para este caso es la fabricación de un depósito cubico con todos los lados iguales.

Este ejemplo es solo una aproximación, pudiendo mejorare con muchos más criterios, como puede ser, usar los precios en lugar de la cantidad de material, incluyendo cortes, mano de obra de soldadura y desperdicios, así como intentar usar chapas enteras para minimizar los desperdicios de material.

Solver métodos de resolución

La herramienta de Microsoft Excel, Solver dispone de tres métodos de resolución de problemas.

Es posible cambiar entre ellos en la sección "Método de resolución" seleccionando el que mejor se adapte mediante el cuadro desplegable.

Las opciones son:

  - GRG Nonlinear

  - Simplex LP

  - Evolutionary

En la misma pantalla se explica cuando usar cada uno de ellos.

A pesar de que el método "GRG Nonlinear" es el método por defecto, para la mayoría de los problemas es suficiente y más rápido usar el "Simplex LP"

A la derecha del desplegable encontramos un botón que nos lleva a una pantalla de configuración de los métodos de resolución.

La primera pestaña es común a todos lo métodos.

La segunda es para el método "GRG Nonlinear"

Y la tercera para el método "Evolutionary"

El mejor enfoque me suele resultar comenzar por el método "Simlex LP", y si con  este no se encuentra el resultado deseado, pasar a uno de los otros dos.