Introducción A Power Query M Code

Power query es una herramienta muy potente de extracción y transformación de datos que viene horneada en Excel 2016 (o posterior), Excel para Office 365 y Power BI.

Se puede encontrar en la pestaña de Datos en la sección Get & Transform Data de la cinta.

Es muy potente y también muy fácil de usar y el editor de consultas tiene una interfaz de usuario súper intuitiva para un usuario de Excel. Muchos pasos de transformación se pueden realizar fácilmente desde la cinta del editor de consultas de potencia y no necesita conocer ningún código para limpiar y dar forma a sus datos.

Detrás de las escenas del editor fácil de usar, Excel está traduciendo cada paso en su proceso de transformación de esos comandos de la cinta en el lenguaje de código de consulta de potencia M.

Este post le presentará los fundamentos del lenguaje de código M de power query y asumirá que ya conoce los fundamentos de power query.

¿Qué es el código M?

La M significa Mash-up de datos, ya que power query trata de conectarse a varias fuentes de datos diferentes y «machacarlas».

El código M es el lenguaje detrás de las escenas de power query. Cuando se crea una transformación de datos en la interfaz de usuario del editor de power query, Excel está escribiendo el código M correspondiente para la consulta.

M es un lenguaje funcional, lo que significa que se escribe principalmente con funciones que se llaman para evaluar y devolver resultados. El código M viene con una biblioteca muy grande de funciones predefinidas disponibles y también puede crear las suyas propias.

¿Dónde se puede escribir el código M de Power Query?

Si quieres empezar a escribir o editar código M, vas a necesitar saber dónde puedes hacerlo. Hay dos lugares donde es posible, en la barra de fórmulas o en el editor avanzado.

La barra de fórmulas

Por cada paso que se crea en la UI del editor puede ver el código M correspondiente en la barra de fórmulas.

Si no ve la barra de fórmulas, vaya a la pestaña Ver y asegúrese de que la opción Barra de Fórmulas está marcada.

Puede editar el código M de cualquier paso de una consulta haciendo clic en la fórmula y editando el código existente. Cuando haya terminado, puede aceptar los cambios haciendo clic en la marca de verificación o pulsando Intro. También puede descartar los cambios haciendo clic en la X o pulsando Esc.

También puede crear pasos completamente nuevos en su consulta con la barra de fórmulas haciendo clic en el símbolo fx junto a la barra de fórmulas. Esto creará un nuevo paso que hace referencia al paso anterior por su nombre y luego puede crear cualquier código M que necesite.

El editor avanzado

La barra de fórmulas sólo muestra el código M para el paso actualmente seleccionado en la consulta, pero el editor avanzado es donde puede ver y editar el código M para toda la consulta.

Puede abrir el editor avanzado desde dos lugares en la cinta del editor. Desde la pestaña Inicio o desde la pestaña Vista, pulse el botón Editor avanzado.

A pesar del apelativo «avanzado», el editor es el editor de código más básico que verá y no contiene (todavía) ninguna función de autocompletado con intellisense, resaltado de sintaxis o autoformateo.

El editor avanzado mostrará el nombre de la consulta, mostrará el código M para la consulta y mostrará una advertencia sobre cualquier violación de sintaxis en el código M. Eso es todo!

Biblioteca de funciones estándar

Dado que el código M es un lenguaje funcional, todo gira en torno a las funciones y el código M viene con una gran biblioteca de funciones predefinidas llamada biblioteca estándar.

La información sobre todas las funciones de la biblioteca estándar disponibles se puede encontrar en la página web de Microsoft Power Query M Reference, incluyendo la sintaxis de las funciones y ejemplos.

La biblioteca estándar también se puede explorar desde el editor de Power Query utilizando la palabra clave #compartida.

Cuando se introduce en la barra de fórmulas, se pueden explorar todas las funciones disponibles haciendo clic en la palabra Función a la derecha del nombre de la función. Encontrará la misma sintaxis y ejemplos que en la página web de referencia.

Sensibilidad a las mayúsculas y minúsculas

Una de las primeras cosas que alguien debe tener en cuenta al escribir código M es que es un lenguaje sensible a las mayúsculas y minúsculas.

Esto significa que x no es lo mismo que X o que «abc» no es lo mismo que «ABC». Esto es cierto para cualquier valor, variables, funciones, etc.

Expresiones y Valores en Power Query

Power query es todo acerca de Expresiones y Valores.

Una expresión es algo que puede ser evaluado para devolver un valor en power query. 1 + 1 es una expresión que se evalúa al valor 2.

Un valor es una pieza única de datos. Los valores pueden ser valores individuales como números, texto, lógicos, nulos, binarios, fecha, hora, datetime, datetimezone o duraciones.

Los valores también pueden tener estructuras más complejas que los valores individuales como listas, registros y tablas. También puede tener valores que son una combinación de listas, registros y tablas. Listas de listas, tablas de listas, tablas de tablas, etc… son todas las posibles estructuras de valores.

Valores literales simples

Los valores literales simples son el bloque de construcción básico de todos los demás valores.

• 123,45 es un valor numérico.
• «¡Hola Mundo!» es un valor de texto.
• verdadero es un valor lógico.
• nul representa la ausencia de un valor.

Valores intrínsecos simples

Los valores intrínsecos se construyen utilizando las diversas funciones intrínsecas.

• #time(horas, minutos, segundos)
• #date(años, meses, días)
• #datetime(años, meses, días, horas, minutos, segundos)
• #datetimezone( años, meses, días, horas, minutos, segundos, offset-hours, offset-minutes)
• #duration(days, hours, minutes, seconds)

Por ejemplo, para construir la fecha 2018-12-31 habría que construirla usando la función intrínseca #date(2018, 12, 31).

Valores estructurados

Listas

Una Lista es una secuencia ordenada de valores.

Se puede definir una lista utilizando llaves. {1, 2, 3} es una lista que contiene los números 1, 2 y 3. Como el orden es importante, no es la misma lista que {3, 2, 1}.

{«Hola», «Mundo»} es una lista que contiene el texto «Hola» y «Mundo».

También son posibles las listas de listas, así {{1, 2}, {3, 4, 5}} es una lista de dos listas. La primera lista contiene el número 1 y 2 y la segunda lista contiene los números 3, 4 y 5.

Se pueden crear listas secuenciales utilizando el formato {x..y}. {2..5} producirá la lista {2, 3, 4, 5}. Esto también funciona para los caracteres de texto. {«a».. «d»} producirá la lista {«a», «b», «c», «d»}.

También se puede tener una lista sin elementos, {} es la lista vacía.

Como las listas están ordenadas, podemos referenciar los elementos de la lista con un número de índice basado en cero. {1, 2, 3}{2} se evaluará a 3 ya que este es el segundo elemento de la lista (basado en un índice cero).

Registros

Un Registro es una secuencia ordenada de Campos. Cada campo consta de un nombre de campo que lo identifica de forma única y un valor de campo que puede ser cualquier tipo de valor.

Se puede definir un registro utilizando llaves cuadradas. es un registro con dos campos. El primer campo del registro tiene un nombre de campo de FirstName y un valor de «John». El segundo campo en el registro tiene un nombre de campo de Edad y un valor de 38.

Los registros de registros también son posibles, ] es un registro con un campo con un nombre de campo de Persona y un valor de campo que es un registro.

Los registros vacíos también son posibles, es el registro vacío.

Puede hacer referencia al valor del campo en un registro por su nombre de campo. evaluará a «Juan».

Tablas

Una Tabla es una secuencia ordenada de Filas donde cada fila es una lista.

Las tablas sólo se pueden construir utilizando una función intrínseca. Puede construir una tabla utilizando la función #table() a partir de una lista de encabezados de columna y una lista de filas.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}})Creará una tabla con 2 columnas, 3 filas y los encabezados de columna de Letras y Números.

Es posible crear una tabla vacía utilizando listas vacías en la función intrínseca #table(). #table({}, {}) producirá una tabla vacía.

Se puede referenciar cualquier valor en una tabla con el índice de fila basado en cero y el nombre del encabezado de la columna. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} se evaluará a «C», ya que es la segunda fila (basado en un índice cero) de la columna Letras.

Expresiones

Las expresiones son cualquier cosa que se puede evaluar a un valor. Esto es cierto para los valores mismos. Por ejemplo, la expresión 1 se evalúa al valor 1.

Aunque normalmente se piensa en las expresiones como si estuvieran formadas por operaciones o funciones más complejas.

Por ejemplo:

• La expresión 1 + 1 se evalúa a 2.
• La expresión 3 > 2 se evalúa a verdadero.
• La expresión «Hola » & «Mundo» se evalúa a «Hola Mundo».
• La expresión Text.Upper(«Hola Mundo») se evalúa a «HOLA MUNDO».

Operadores

Además de la biblioteca estándar, el código M también tiene una lista de funciones especiales llamadas operadores. Estos toman exactamente dos valores (o expresiones que se evalúan a un valor) y devuelven un solo valor.

Aritmética

El código M viene con los operadores aritméticos básicos que usted esperaría y está acostumbrado a de Excel regular +, -, * y /. Estos le permitirá sumar, restar, multiplicar y dividir los valores, respectivamente.

Estos pueden ser utilizados con varios otros tipos de valores que no sólo los números. Por ejemplo, puede añadir una duración a una fecha.

#date(2018,12,25) + #duration(7, 0, 0, 0) se evaluará a 2019-01-01.

Comparación

Puede comparar valores en el código M utilizando los operadores de comparación <, >, <=, >=, =, <>.

• x < y se evaluará como verdadero si x es menor que y.
• x > y se evaluará como verdadero si x es mayor que y.
• x <= y se evaluará como verdadero si x es menor o igual que y.
• x >= y se evaluará como verdadero si x es mayor o igual que y.
• x = y se evaluará como verdadero si x es igual a y.
• x <>y se evaluará como verdadero si x no es igual a y.

Se pueden utilizar con varios tipos de valores. Por ejemplo, puede comparar dos listas con el operador igual.

{1,2,3,4} = {1,2,3} se evaluará a falso ya que las listas no son iguales.

Concatenación y Fusión

Puede concatenar texto y fusionar listas, registros y tablas utilizando el operador ampersand &.

Por ejemplo:

"Hello " & "World"se evaluará a «Hola Mundo».

{1,2,3} & {3,4,5} se evaluará a {1,2,3,3,4,5}.

Lógica

Puede realizar operaciones sobre valores booleanos (o expresiones que se evalúan a valores booleanos) con los operadores not, and y or.

Comentando código

Como es de esperar en cualquier lenguaje de programación, es posible añadir comentarios a su código.

Hay dos tipos de comentarios posibles en el código M. Los comentarios de una sola línea y los comentarios de varias líneas.

Comentarios de una sola línea

M code goes hereM code goes here //This is a single line commentM code goes here

Un comentario de una sola línea se puede crear precediendo el comentario con dos caracteres de barra diagonal //. Todo lo que esté en la misma línea antes de esto se interpretará como código M, todo lo que esté después se interpretará como un comentario.

Comentarios de varias líneas

M code goes here /*This is a commenton multiple lines*/ M code goes here

Se puede crear un comentario de varias líneas colocando el comentario entre los caracteres /* y */. Todo lo que esté fuera de ellos se interpretará como código M. Cualquier cosa entre estos se interpretará como un comentario.

Sentencia Let

La sentencia let permite evaluar un conjunto de valores y asignarlos a nombres de variables para luego utilizarlos en una expresión posterior que sigue a la sentencia in.

let a = 1, b = 2, c = a + bin c

Esta expresión se compone de tres expresiones que se evalúan después de la sentencia let. Cada expresión está separada por una coma, excepto la última antes de la sentencia in. En este ejemplo, toda la expresión let e in se evaluará como 3.

let c = a + b, b = 2, a = 1in c

Puede que pienses que las expresiones dentro de una sentencia let tienen que aparecer en el orden en que deben ser evaluadas. Esto no es así. El código anterior es perfectamente válido y también se evaluará a 3. El evaluador de código M calculará automáticamente el orden de los cálculos necesarios basándose en las dependencias de las expresiones.

Obviamente es más fácil para una persona leer el código M si está escrito en orden de evaluación, pero también hay otra ventaja. Las expresiones aparecerán como pasos separados en la ventana de Pasos Aplicados. Cuando se escriben fuera de orden, las expresiones aparecerán como un paso combinado.

let a = 1, b = 2in a + b

También puede evaluar expresiones dentro de la parte in de una expresión let… in….

Nombres de variables

let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"

Puede asignar casi cualquier nombre a sus expresiones utilizando los caracteres #»». Incluso puede utilizar caracteres de espacio y otros caracteres especiales. El uso de palabras clave reservadas es la única excepción.

Los nombres de las variables son los que aparecerán en los Pasos Aplicados del editor de consultas, por lo que poder utilizar caracteres de espacio es una gran característica.

Expresiones Each

La expresión each es una forma abreviada de declarar funciones que toman un único parámetro llamado _ (guión bajo).

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"

En este ejemplo estamos creando una nueva columna que multiplica la columna Números por 2 para cada fila.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"

Podemos crear la misma consulta utilizando la sintaxis del guión bajo que es semánticamente equivalente a la expresión each. Ambas consultas funcionarán igual.

Sentencias If Then Else

El código de M es bastante escaso en comparación con otros lenguajes cuando se trata de expresiones lógicas. No hay sentencias select case o loop disponibles. Sólo hay una expresión if… then… else… disponible.

if then else 

La sintaxis es directa y es como la mayoría de otros lenguajes de programación. Puede aparecer todo en una línea, o puede presentarse en líneas separadas para facilitar la lectura.

Try Otherwise Statements

Los errores pueden ocurrir cuando se intenta realizar operaciones que requieren tipos particulares de datos. Por ejemplo, intentar multiplicar un número por un valor de texto dará lugar a un error.

let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"

Los errores se pueden evitar utilizando la expresión try… otherwise…

Esto evitará errores en los resultados de su consulta y le permitirá reemplazar los errores con cualquier valor o expresión.

Funciones

Una función es un mapeo de un conjunto de valores de parámetros a un valor. Junto con las funciones de la biblioteca estándar, el código M le permite crear sus propias funciones.

let Product = (x,y) => x * y, Result = Product(2,3)in Result

Esta consulta define una función que multiplica dos números. Entonces la consulta llama y evalúa la función con los valores 2 y 3 que evalúa a 6.

Funciones con parámetros opcionales

Hay dos tipos de parámetros de función, un parámetro requerido y un parámetro opcional.

Los parámetros requeridos deben especificarse siempre cuando se invoca una función.

Los parámetros opcionales no necesitan especificarse cuando se invoca una función. Si el parámetro opcional falta, entonces el valor pasado a la función será nulo.

let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in Result

Esta función tiene un segundo argumento opcional. Entonces la consulta llama y evalúa la función utilizando sólo el parámetro requerido con un valor de 2, que se evalúa a 2. Tenga en cuenta que la función tiene que tener en cuenta que y es nulo, de lo contrario un argumento opcional que falta podría resultar en una función que evalúa a un error.

Funciones recursivas

También es posible escribir una función que se refiere a sí mismo mediante el uso de la @ operador de alcance.

let Fibonacci = (n) => if n = 1 then 1 else if n = 2 then 1 else @Fibonacci(n-1) + @Fibonacci(n-2), Result = Fibonacci(7)in Result

La secuencia de Fibonacci es un ejemplo de una función que se define recursivamente. El siguiente número de la secuencia se define como la suma de los dos números anteriores. Así que para obtener el enésimo número, necesitas saber el (n-1)º y el (n-2)º número.

Esta función encontrará el enésimo número de Fibonacci sumando el (n-1)º y el (n-2)º número de Fibonacci.

La consulta se evalúa a 13, ya que 13 es el séptimo número de Fibonacci.

Aquí hay algunos ejemplos más útiles en los que se pueden utilizar funciones recursivas para replicar la función TRIM de Excel para eliminar el exceso de espacios entre las palabras o buscar y reemplazar en masa los valores basados en una lista.

Funciones de consulta

Los ejemplos anteriores definieron una función dentro de una consulta y luego llamaron y evaluaron la función dentro de la consulta.

También es posible crear una consulta que es una función (una función de consulta) y puede ser llamada y evaluada desde otras consultas.

let FunctionResult = (Argument1, Argument2,...) => let /*M code to evaluate in the function goes here*/ in Resultin FunctionResult

Este es el formato general necesario para crear una función de consulta. Tenga en cuenta que necesitará una sentencia let… in… dentro de la sentencia let… in… de la función de consulta para poder realizar múltiples pasos.

Conclusiones

Power query es una gran característica de Excel que puede ayudarle a automatizar y simplificar su importación y transformación de datos.

Puede hacer mucho utilizando sólo la interfaz gráfica de apuntar y hacer clic sin tocar nunca el código M. Pero a medida que sus requisitos se vuelven más avanzados, puede haber un momento en el que necesite editar el código M que ha creado o escribir el suyo propio desde cero.

Cualquier lenguaje de programación nuevo es ilegible si no se conocen los fundamentos primero. Esta guía le permitirá ponerse al día con el código M para que pueda empezar a crear consultas más avanzadas.