Lenguaje M en Power Query. Parte 1

Vamos a aprender a crear consultas en lenguaje M, decir, que el lenguaje M. es un lenguaje que distingue entre mayúsculas, y, minúsculas.

Con Power Query, podemos extraer e importar datos, limpiar, y, transformar los datos.

Es una herramienta ETL (Enabled-Transform-Loading), extracción, transformación, y, carga.

Power Query, lo tenemos en Excel, y, Power BI.

Con Power Query, podemos reemplazar funciones de Excel.

En los videos anteriores, cada vez que hemos creado una consulta, detrás de nosotros, se ha generado el código M.

Para crear una consulta, debemos de usar el editor avanzado, donde tenemos que crear variables, y, expresiones con una variable LET.

Voy a abrir el ejemplo del video anterior.

Vemos en la parte derecha, la ventana de consultas y conexiones, donde tenemos nuestra consulta.

Hacemos doble clic en la consulta, para acceder a Power Query.

En la parte izquierda, vemos el panel de consultas.

En la parte derecha, vemos la ventana configuración de la consulta, donde tenemos el nombre de la consulta, y, pasos aplicados.

Cuando nos traemos un modelo a Power Query, se crean algunos pasos de forma automática, como el paso de tipo cambiado.

Si hacemos clic en cualquiera de los pasos, veremos el código M en la barra de fórmulas, recordar que el código M, distingue entre mayúsculas, y, minúsculas.

Voy a duplicar la consulta.

Vamos a seleccionar el paso origen, vamos a la barra de fórmulas, y, vemos que carga un modelo llamado ventas, y, todo el contenido, es decir, todas las columnas.

= Excel.CurrentWorkbook(){[Name="Ventas"]}[Content]

Pero, si quisiera solo una columna, después de Content, abrimos otros corchetes, y, ponemos el nombre de la columna, por ejemplo:

= Excel.CurrentWorkbook(){[Name="Ventas"]}[Content][Centro]

Obtenemos una lista de valores, que son los centros.

Vemos que aparece seleccionada la pestaña transformar, pues hacemos clic en a la tabla.

Se abre la ventana a la tabla, dejamos los valores que aparecen, y, aceptamos.

Ya tenemos los centros como tabla, pero con duplicados.

En la parte central, los datos.

Vamos a ir al editor avanzado, podemos acceder desde la pestaña de inicio, entro del grupo consulta.

También, podemos acceder desde la pestaña vista, dentro de uso avanzado.

Pues, hacemos clic en editor avanzado.

Se abre la ventana de editor avanzado, donde aparece el nombre de la tabla.

En la ventana central, tenemos las líneas que compone nuestra consulta.

Como he dicho anteriormente, toda expresión, comienza con LET, y, debajo de LET, están los pasos de la consulta.

En este caso, tenemos tres pasos.

Si nos fijamos en pasos aplicados, vemos que tenemos dos pasos.

Si nos fijamos en el editor avanzado, vemos los dos nombres, junto a su cálculo.

Vamos a ver como se cambia el código M, cuando cambiamos el nombre de un paso aplicado, voy a seleccionar el primer paso, y, presión F2.

Como nombre le pongo pruebas.

Volvemos al editor avanzado, y, vemos como aparece dicho nombre.

Como resultado dentro de IN, siempre tendremos la última expresión, que es nuestra salida, es decir, cada transformación empieza con LET, debajo de LET, tenemos las transformaciones, y, en IN, tenemos el resultado final.

Cada paso de transformación comienza con una almohadilla, y, un nombre, entre comillas dobles.

#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Fecha", type datetime}

Después, vemos, por ejemplo, Fecha entre comillas dobles, entonces, cuando va precedido de la almohadilla, quiere decir que es el nombre de un paso de transformación.

¿Por qué el primer paso, no tiene almohadilla, no está entre comillas dobles?

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

Porque no tiene espacios, cuando tenemos un nombre sin espacios, no hace falta poner la almohadilla, ni el texto entre comillas dobles.

Cada línea de transformación debe de terminar con coma (,), esto quiere decir que pase a la siguiente transformación, y, cuando no hay coma, quiere decir que no hay más líneas de transformaciones, y, pasa a IN, donde aparece la última transformación.

Siempre que tenemos una transformación, hace referencia al paso anterior, porque dicha línea actúa sobre el paso anterior.

let

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

#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Fecha", type datetime},

Veamos un ejemplo sencillo, de cómo crear una tabla, en este caso, con un solo registro.

Voy a duplicar la consulta, para ello, hago clic en la consulta, y, selecciono referencia, no duplicado.

Vamos al editor avanzado.

Vemos que la variable origen, toma los valores de Table1.

Borramos Table1, ponemos Table.FromRecorders, y, abrimos un paréntesis.

Esta función, nos permite agregar registros a una tabla.

Abrimos una llave.

let

Origen = Table.FromRecords(

{

Abrimos un corchete para introducir el primer elemento, lo primero es el nombre de la columna, que va a ser Elemento, y, le damos un valor.

let

Origen = Table.FromRecords(

{

[Elemento=1

Cerramos corchete, y, cerramos la llave.

let

Origen = Table.FromRecords(

{

[Elemento=1]

}

Cerramos el paréntesis de Table.FromRecorders.

let

Origen = Table.FromRecords(

{

[Elemento=1]

}

)

En la variable IN, ponemos Origen.

let

Origen = Table.FromRecords(

{

[Elemento=]

}

)

in

Origen

En la parte inferior, vemos que no se han detectado errores.

Clic en listo.

Vemos que se ha creado una consulta, de una columna, llamada Elemento, con el valor 1.

Voy a agregar una nueva columna, que será nombre.

let

Origen = Table.FromRecords(

{

[Elemento = 1, Nombre = "Miguel"]

}

)

in

Origen

Vemos como aparece dicha columna.

Voy a agregar un nuevo registro, para añadir un nuevo registro, después del primer registro, debemos de poner una coma.

let

Origen = Table.FromRecords(

{

[Elemento = 1, Nombre = "Miguel"],

[Elemento = 2, Nombre = "Claudia"]

}

)

in

Origen

Clic en listo, y, ya tenemos el registro agregado.

Si nos fijamos en el código, vemos una llave de apertura, y, de cierre, y, entre ambas llaves, tenemos los registros que hemos dado de alta.

Las llaves delimitan cada expresión.

Expresiones y valores

Una expresión es un cálculo, que devuelve un valor, pero, un valor no es una expresión.

Un valor primitivo es un único valor, como un número, un valor lógico, texto o un valor NULL.

Siguiente con el ejemplo anterior, el numero 1 es un valor primitivo.

Claudia, también es un valor primitivo.

Datos estructurados

El lenguaje M admite datos estructurados, como listas, registro, tabla.

Una lista es una secuencia ordenada de base cero de valores delimitados por caracteres de llave { }, el lenguaje M admite un numero infinito de valores, excepto, si es texto, entonces, debe de tener una longitud fija, por ejemplo, {1;2}, tiene una longitud de 2.

Por ejemplo, el siguiente código, crea una lista con los valores 1,2, y, 3.

let

Origen =

{1,2,3}

in

Origen

El siguiente código crea una columna de dos filas, en la primera fila, con los numero 1,2, y, 3, y, la segunda fila con los números 4,5, y, 6.

let

Origen =

{

{1,2,3},

{4,5,6}

}

in

Origen

El siguiente código crea dos registros de dos columnas, una llamada nombre, y, otra llamada teléfono.

let

Origen =

{

[Nombre="Jaime", Telefono=123],

[Nombre="Claudia", Telefono=456]

}

in

Origen

Vemos que se activa la pestaña transformar, hacemos clic en a la tabla.

Ahora, expandimos haciendo clic en el siguiente icono.

Tenemos una tabla con ambos registros.

La siguiente expresión, obtiene el segundo valor del primer elemento.

let

Origen =

{

{1,2,3},

{4,5,6}}

{0}{1}

in

Origen

Un registro es un conjunto de campos. Un campo se compone de un nombre, y, un valor, donde nombre es un valor de texto único, podemos escribir los nombres sin comillas, y, sin formato que también se conoce como identificadores.

Por ejemplo, el siguiente código, añade dos registros.

Una tabla es un conjunto de valores organizados en columnas, que se identifican por nombre y filas, igual que en Excel. Podemos usar varias funciones para crear tablas a partir de listas o registros.

En el siguiente ejemplo, se crea una tabla de dos columnas, llamadas Col1, y, Col2, con los valores para Col1, 1 y 2, y, los valores de Col2, son 3 y 4.

El siguiente código, crea una tabla de un solo registro con un encabezado, y, un registro.

let

Source = #table(

{"IDproducto", "Producto", "Precio"},

{

{1, "Aspiradora", 99}

})

in

Source

Con el siguiente código, añadimos un registro.

let

Source = #table(

{"IDproducto", "Producto", "Precio"},

{

{1, "Aspiradora", 99},

{2, "Frigorifico", 189}

})

in

Source

Si quiero poner los encabezados, pero sin tener que poner las comillas dobles, sustituyo la línea donde hemos puesto los encabezados por la siguiente.

let

Source = #table(

type table [IDproducto, Producto, Precio],

{

{1, "Aspiradora", 99},

{2, "Frigorifico", 189}

})

in

Source

Si quiero obtener el segundo registro, en la instrucción IN, en source, añado {1}.

Let

Source = #table(

type table [Idproducto, Producto, Precio],

{

{1, “Aspiradora”, 99},

{2, “Frigorifico”, 189}

})

in

Source{1}

Cuando nos traemos un modelo desde Excel, Power Query, usa la función Excel.CurrentWorkbook, que nos devuelve el libro actual, me voy a llevar a Power Query, del modelo de provincias, la hoja de Sevilla.

Seleccionamos el paso origen, y, miramos en la barra de fórmulas, vemos la función Excel.CurrentWorkbook, donde se abre y se cierra paréntesis, que obtiene los datos del libro actual.

= Excel.CurrentWorkbook()

A continuación, se abre una llave, un corchete, como nombre de tabla he puesto Sevilla, se cierra corchete, y, llave.

= Excel.CurrentWorkbook(){[Name="Sevilla"]}

Se abre un corchete, y, aparece la palabra Content, y, se cierra corchete, quiere decir, que devuelva todas las columnas.

= Excel.CurrentWorkbook(){[Name="Sevilla"]}[Content]

Si cambio Sevilla por Cádiz, obtengo la tabla de Cádiz.

= Excel.CurrentWorkbook(){[Name="Cadiz"]}[Content]

Si después de Content, entre corchete pongo, por ejemplo, provincia, obtengo la columna de provincia.

= Excel.CurrentWorkbook(){[Name="Cadiz"]}[Content][Provincia]

Si, simplemente, ponemos:

= Excel.CurrentWorkbook()

Obtenemos todas las provincias, pero dichas provincias, debe de estar en formato de tabla.

Una función es un valor con argumentos, que devuelve un nuevo valor.

Una expresión, puede hacer referencia a otros elementos de la expresión por su nombre.

En el siguiente ejemplo, se crea una variable llamada A1, con el valor 2, después, se crea otra variable llamada A2, que multiplica el valor de la variable A1 por 2.

Un registro también puede incluir una lista, con el operador de índice posicional ({}), podemos acceder a un elemento de la lista por su índice, en base cero, es decir, que el primer elemento tiene como índice el valor cero.

Un identificador puede tener las dos formas siguientes:

· nombre_identificador, se puede escribir como ProductoID.

· #"nombre del identificador", se puede escribir como #"Agregar columna:".

En el siguiente ejemplo, se crean dos tablas, cada tabla es del año 2020, y, 2021, cada tabla tiene un campo llamado PrimeraVenta, y, SegundaVenta con un valor, después, creamos una variable donde tomamos el campo PrimeraVenta de la tabla 1, ventas{0}, y lo sumamos al campo PrimeraVenta de la segunda tabla, ventas{1}, al hacer clic en listo, vemos que nos devuelve 7000.

Para crear una función, debemos de ponerle un nombre, después, el símbolo =>, y, un valor, por ejemplo, la siguiente línea, crea una variable llamada A, y el valor, es el valor de la variable A más 1.

Al hacer clic en listo, vemos que nos pide un valor para la variable A.

Voy a poner el valor 2, y, hago clic en invocar.

Vemos que nos devuelve 3.

Decir, que el valor de la variable A es opcional, pero si no lo ponemos nos devuelve NULL.

En el siguiente ejemplo, se crea un registro con nombre, edad, y, población.

let

Origen =

[

Nomnbre = "Jaime",

Edad=34,

Localidad="Sevilla"

]

in

Origen

Si lo quiero convertir en tabla, en la pestaña de transformar, hacemos clic en a la tabla.

Hacemos clic en el siguiente icono, para expandir.

Con la siguiente expresión, creamos dos registros, para crear más de un registro deben de ir encerrado entre llaves.

let

Origen = {

[

Nomnbre = "Jaime",

Edad=34,

Localidad="Sevilla"

],

[

Nomnbre = "Claudia",

Edad=38,

Localidad="Cadiz"

]}

in

OrigenExpresión LET

La expresión LET es una expresión, y, dentro de LET cada línea es una expresión, recordar que una expresión devuelve un valor.

Dentro de una expresión LET, podemos tener tantas expresiones como deseemos.

Cada expresión, comienza con el nombre del paso, conocido como identificador.

La expresión LET encapsula un conjunto de valores que se van a calcular, a los que se le asignan nombres que se van a usar en una expresión subsiguiente que sigue a la instrucción in.

Veamos un ejemplo sencillo, abrimos le editor avanzado, ponemos LET, ponemos origen, que es igual a:

let

Origen = Text.Proper("hola")

La función Text.Proper, pone en mayúscula el primer carácter.

En la instrucción IN ponemos origen.

let

Origen = Text.Proper("hola")Expresión LET

La expresión LET es una expresión, y, dentro de LET cada línea es una expresión, recordar que una expresión devuelve un valor.

Dentro de una expresión LET, podemos tener tantas expresiones como deseemos.

Cada expresión, comienza con el nombre del paso, conocido como identificador.

La expresión LET encapsula un conjunto de valores que se van a calcular, a los que se le asignan nombres que se van a usar en una expresión subsiguiente que sigue a la instrucción in.

Veamos un ejemplo sencillo, abrimos le editor avanzado, ponemos LET, ponemos origen, que es igual a:

let

Origen = Text.Proper("hola")

La función Text.Proper, pone en mayúscula el primer carácter.

En la instrucción IN ponemos origen.

let

Origen = Text.Proper("hola")

Expresión LET

La expresión LET es una expresión, y, dentro de LET cada línea es una expresión, recordar que una expresión devuelve un valor.

Dentro de una expresión LET, podemos tener tantas expresiones como deseemos.

Cada expresión, comienza con el nombre del paso, conocido como identificador.

La expresión LET encapsula un conjunto de valores que se van a calcular, a los que se le asignan nombres que se van a usar en una expresión subsiguiente que sigue a la instrucción in.

Veamos un ejemplo sencillo, abrimos le editor avanzado, ponemos LET, ponemos origen, que es igual a:

let

Origen = Text.Proper("hola")

La función Text.Proper, pone en mayúscula el primer carácter.

En la instrucción IN ponemos origen.

let

Origen = Text.Proper("hola")

in

Origen

Hacemos clic en listo, y, vemos como la palabra hola, aparece con la primera letra en mayúsculas.