CURSO: DATA ANALYTICS
POWER BI – NIVEL INTERMEDIO
Capítulo 3: Transformación de
datos con Power Query M
Objetivos
Al finalizar el capítulo, el alumno logrará:
• Comprender los conceptos de Lenguaje M y su importancia en la
transformación de los datos.
• Identificar y aplicar funciones básicas del lenguaje M
2
Agenda
1. Introducción Lenguaje M
2. Columna personalizada - Lenguaje M
3. Funciones de Power Query - Lenguaje M
̶ [Link]
̶ [Link]
̶ [Link]
̶ [Link]
̶ [Link]
̶ [Link]()
̶ [Link] ()
̶ [Link]
̶ [Link]
̶ [Link]
4. División Condicional con Lenguaje M
5. Columna Condicional con Lenguaje M
6. Agrupar datos con Lenguaje M
7. Transponer datos con Lenguaje M
8. Importar archivos desde una carpeta con Lenguaje M
3
1. Introducción Lenguaje M
• El lenguaje M es básicamente el idioma de programación con el que trabaja el
editor de consultas Microsoft Power Query
• Utilizado para poder conectar, combinar y refinar los orígenes de datos y así
dejar una base de datos limpia que permita extraer y analizar la información
que se desee.
• Se visualiza el lenguaje M en la barra de fórmulas que se muestra entre la
cinta de opciones Vista.
4
1. Introducción Lenguaje M
• ¿Cómo se crea este lenguaje M?
5
1. Introducción Lenguaje M
Valor de entrada
Valor de Salida
6
1. Introducción Lenguaje M
• Ejemplo 1
− Ingresamos valores de entrada
− Para obtener un resultado
− un valor de salida
7
1. Introducción Lenguaje M
Clases de Valores en M
Valores Primitivos Valores Estructurados
Valores Atómicos Valores Compuestos
• Numéricos (number) • Lista (list)
• Fecha (date) • Registro (record)
• Hora (time) • Table (table)
• Fecha y Hora (datetime) • Funciones (function)
• F.H y Z (datetimezone) • Tipo (types)
• Duración (duration)
• Texto (text)
• Nulo (null)
• Binarios (binary)
8
1. Introducción Lenguaje M
• Ejemplo 2
− Ingresamos valores de entrada
de tipo lista
− Una lista de salida
9
1. Introducción Lenguaje M
• Ejemplo 3
− Ingresamos valores de entrada
de tipo tabla
− Una tabla como salida
10
1. Introducción Lenguaje M
• Consideraciones Lenguaje M:
− Intellisense
Dentro del Editor avanzado, tendremos la facilidad de escribir nuestro código con la ayuda
de un menú desplegable para elegir una función o alguna variable que hallamos creado en
los pasos anteriores de la consulta.
11
1. Introducción Lenguaje M
• Consideraciones Lenguaje M:
− Cace Sensitive
El motor de Power Query distingue entre mayúsculas y minúsculas. Aplica tanto nombres
de variales, valores en tabla, identificadores o funciones propias del lenguaje.
Por ejemplo : La variable Prueba diferente que prueba
12
1. Introducción Lenguaje M
• Consideraciones Lenguaje M:
− Desplazamiento de Base Cero
La primera posición siempre iniciará de cero. En Excel, sucede distinta ya que se inicia a
partir del número 1.
Por ejemplo : Si tenemos la frase Nueva Consunta podemos decir que:
▪ La letra u esta en la posición 1
▪ La letra C esta en la posición 7
13
2. Columna Personalizada
• Permiten definir el valor de una columna nueva mediante una formula
personalizada.
• Las formulas pueden incluir lenguaje M.
14
3. Funciones de Power Query - Lenguaje M
• [Link]: Devuelve una función que divide el texto en
una lista de texto en cada delimitador especificado en secuencia.
• Ejemplo:
let
startAtEnd = true
in
[Link]({",", ";"},
[Link], startAtEnd)("a,""b;c"",d")
15
3. Funciones de Power Query - Lenguaje M
• [Link]: Devuelve una función que divide el texto en
una lista de texto en cualquiera de los delimitadores especificados.
• Ejemplo:
let
startAtEnd = true
in
[Link]({",", ";"},
[Link], startAtEnd)("a,""b;c,d")
16
3. Funciones de Power Query - Lenguaje M
• [Link]: Devuelve los valores de una columna en una lista.
• [Link]: Devuelve una tabla a partir de una lista de registros.
• Ejemplo:
[Link](
[Link]({
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"]
}),
"Name"
)
17
3. Funciones de Power Query - Lenguaje M
• [Link]: Convierte una lista en una tabla aplicando la función de
división opcional a cada elemento de la lista.
• Ejemplo:
[Link]({"a", "b", "c", "d"}, null, {"Letters"})
18
3. Funciones de Power Query - Lenguaje M
• [Link]: Convierte columnas en filas y filas en columnas.
• Ejemplo:
[Link](
[Link]({
[Name = "Nombre completo", Value = "Fred"],
[Name = "Años", Value = 42],
[Name = "País", Value = "Reino Unido"]
})
)
19
3. Funciones de Power Query - Lenguaje M
• [Link]: Convierte una tabla en una lista aplicando la función de
combinación especificada a cada fila de valores en la tabla.
• [Link]: Devuelve una función que combina una
lista de texto en un solo texto usando el delimitador especificado
• Ejemplo:
[Link](
[Link]({
{[Link](1), "Bob", "123-4567"},
{[Link](2), "Jim", "987-6543"},
{[Link](3), "Paul", "543-7890"}
}),
[Link](",")
)
20
3. Funciones de Power Query - Lenguaje M
• [Link]: Devuelve los valores mas grandes de una columna de tabla.
• Ejemplo:
[Link] ([Details], “Sales")
Tabla : Details
Campo : Sales
Resultado
21
3. Funciones de Power Query - Lenguaje M
• [Link]: Devuelve una tabla de filas de table, que coincide con la
selección condition.
• Ejemplo:
[Link](
[Link]({
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"],
[CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
}),
each not [Link]([Name], "B")
)
22
3. Funciones de Power Query - Lenguaje M
• [Link](): Devuelve el contenido del libro de Excel. En la columna
llamada Content contiene un binario. este binario es el archivo en cuestión de
Excel y con el fin de interpretar el binario necesitamos una función llamada
[Link]().
• [Link] (): Devuelve el contenido del documento CSV como una tabla.
• Ejemplo:
[Link]( [Content])
[Link]([Content])
23
3. Funciones de Power Query - Lenguaje M
• [Link]: Devuelve verdadero si el valor del texto (Parte A) comienza
con el valor del texto (Parte B).
• Ejemplo:
[Link] ("Hello, World", "hello")
[Link]("Hello, World", "Hello")
24
3. Funciones de Power Query - Lenguaje M
• [Link]: Promueve la primera fila de valores como los nuevos
encabezados de columna (es decir, nombres de columna). De forma
predeterminada, solo los valores de texto o números se promueven a
encabezados.
• Ejemplo:
[Link](
[Link]({
[Column1 = "CustomerID", Column2 = "Name", Column3 = "Fecha"],
[Column1 = 1, Column2 = "Bob", Column3 = #date(1980, 1, 1)]
})
)
25
3. Funciones de Power Query - Lenguaje M
• [Link]: Devuelve el número de filas en table
• Ejemplo:
[Link](
[Link]({
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"]
})
)
26
4. División Condicional con Lenguaje M
• Un problema típico de adecuación de datos es transformar para
extraer datos (monto pedido) en otra columna requerida para el
análisis.
Dividir
columna por
delimitador
dinámico
27
Ejercicio Nº 1: División de columna Dinámica
Al finalizar el laboratorio, el alumno logrará:
• Extraer datos de una columna mediante Dividir columna por delimitador
incluyendo funciones M para que la división sea dinámica.
28
5. Columna Condicional con Lenguaje M
• Es una columna que devuelve sus valores en función de algunos criterios.
• Trabaja usando parámetros IF-THEN-ELSE para definir el resultado de sus
campos. No se permite el condicional “And/Or” en una misma condición.
• Para incluir condicional “And” o “Or” en una misma condición se deberá usar
columna personalizada.
29
Ejercicio Nº 2: Columna Condicional con M
Al finalizar el laboratorio, el alumno logrará:
• Extraer datos de una columna mediante Dividir columna por delimitador
incluyendo funciones M para que la división sea dinámica.
30
6. Agrupar datos con Lenguaje M
• Agrupar los valores de varias filas en un solo valor agrupando las
filas según los valores de una o varias columnas
• Permite agrupar mediante una función de agregado o Todas las filas
permitiendo aplicar funciones M para continuar transformado los datos
después de una agrupación.
31
Ejercicio Nº 3: Columna personalizada y Agrupar datos
con M
Al finalizar el laboratorio, el alumno logrará:
• Resumir un conjuntos de filas de una consulta e incluir transformaciones
con funciones M sobre los datos previos a la agrupación.
32
7. Transponer datos con lenguaje M
• A través de la opción Transponer de la pestaña Transformar.
• Permite establecer una columna de pivote.
• Traspone los valores de columna a filas.
33
Ejercicio Nº 4: Transponer datos dinámicamente
Al finalizar el laboratorio, el alumno logrará:
• Transponer datos de varias tabla de Excel con funciones M para
implementarlo dinámicamente, consolidando todas las hojas de un archivo
Excel.
34
8. Importar archivos desde una carpeta
con lenguaje M
• Permite hacer carga masiva de archivos
• Consideraciones:
‒ Cantidad de columnas iguales
‒ Formatos diversos (xls, csv, txt)
‒ Deben incluirse en una carpeta
35
8. Importar archivos desde una carpeta
con lenguaje M
• Combinaciones entre archivos:
̶ [Link]: Muestra los datos actuales que se encuentran dentro del libro de
Excel (representados como una tabla)
̶ [Link]: Es el nombre de la hoja donde se almacenan los datos
̶ [Link]: Es el nombre del elemento, si es un Nombre de Rango entonces va
a ser el nombre de ese rango, para las hojas entonces va a ser el nombre de la
hoja y para las tablas que va a ser el nombre de la tabla.
̶ [Link]: Es el nombre del objeto que se encuentra desde el libro de Excel.
Los más comunes son Hoja, Tabla y DefinedName (Rango Definido o Nombre de
Rango)
36
Ejercicio Nº 5: Importar archivos con diversos formatos
desde una carpeta
Carga masiva de datos.
Al finalizar el laboratorio, el alumno logrará:
• Importar archivos con diversos formatos (xlsx, csv , txt)
• Actualizar los datos de forma dinámica desde una carpeta
37
Tarea Nº 3: Transformaciones con Lenguaje M
Al finalizar la tarea, el alumno logrará:
• Conocer las fuentes de datos y los diferentes formas de transformación a los
datos desde Power Query.
38
Lecturas adicionales
Se sugiere revisar los siguientes enlaces para profundizar en los conceptos
tratados en el presente capítulo:
• Combinar datos y darles forma en Power BI
• Importar datos de orígenes de datos externos
39
Resumen
El editor de consulta apoyara a dar forma a los datos y transformarlos con el
objetivo de que estén listos para sus modelos y visualizaciones.
Logramos comprender el uso de las funciones M que permitirán personalizar y
mejorar nuestros procesos de transformación de los datos.
40