Índice de contenidos

Capítulo 1. Introducción. .......................................................................................... 9

1.1 Introducción a Power Query. .............................................................................. 9

1.1.1 Las cuatro fases de Power Query. ............................................................... 9

1.2 Introducción a Power Pivot. ............................................................................. 13

1.2.1 Características principales de Power Pivot. ............................................... 14

1.2.2 Como se almacenan los datos. .................................................................. 14

1.3 Introducción a DAX. ......................................................................................... 15

1.3.1 Características principales de DAX............................................................ 15

Capítulo 2. Power Query, Power Pivot y DAX. ...................................................... 17

2.1 Primeros pasos Power Query. ......................................................................... 17

2.1.1 Identificación de las diferentes partes de PQ. ............................................ 17

2.1.2 Transformaciones básicas de PQ. ............................................................. 18

2.2 Power Pivot. ..................................................................................................... 20

2.2.1 Identificación de las diferentes partes de PP. ............................................ 20

2.2.2 Transformaciones básicas de PP. ............................................................. 21

2.3 DAX.................................................................................................................. 23

2.3.1 Principales características de DAX. ........................................................... 23

2.3.2 Algunas funciones básicas de DAX. .......................................................... 25

2.3.3 Algunas expresiones básicas de DAX. ...................................................... 26

Capítulo 3. Implementación. ................................................................................... 28

3.1 Instalación de herramientas. ............................................................................ 28

3.2 Creación de base de datos. ............................................................................. 28

Capítulo 4. Conclusiones. ...................................................................................... 39

Capítulo 5. Referencia bibliográfica. ..................................................................... 40

6
Anexo. ...................................................................................................................... 41

7
 Índice de Figura

Imagen 1.1: Fase de Conectar. ................................................................................ 10

Imagen 1.2: Fase de Transformar. ........................................................................... 11
Imagen 1.3: Anexar. ................................................................................................. 12
Imagen 1.4: Combinar. ............................................................................................. 12
Imagen 1.5: Barra Power Pivot. ................................................................................ 13
Imagen 2.1: Power Query 2016 y 365. ..................................................................... 17
Imagen 2.2: Power Query 2010 y 2013. ................................................................... 17
Imagen 2.3: Partes del Editor PQ. ............................................................................ 18
Imagen 2.4: Partes de Power Pivot. ......................................................................... 20
Imagen 2.5: Power Pivot. ......................................................................................... 21
Imagen 2.6: Ventana Power Pivot. ........................................................................... 22
Imagen 2.7: Vista de diagrama. ................................................................................ 22
Imagen 3.1: Disponibilidad de herramientas. ........................................................... 28
Imagen 3.2: Documento sin proceso ETL. ............................................................... 29
Imagen 3.3: Documento con proceso ETL. .............................................................. 29
Imagen 3.4: Tablas cargadas al Modelo de Datos. .................................................. 30
Imagen 3.5: Modelo base de datos. ......................................................................... 30
Imagen 3.6: Medida “$ Ventas”. ............................................................................... 31
Imagen 3.7: Medida Ventas Hombres y Mujeres. ..................................................... 33
Imagen 3.8: Creación de medidas por rango de edades. ......................................... 36
Imagen 3.9: Columnas SexoCliente e ImporteOperacion. ........................................ 37

8
Capítulo 1. Introducción.

Capítulo 1. Introducción.
1.1 Introducción a Power Query.

Power Query (PQ) es un motor de transformación y preparación de datos. Viene con

una interfaz gráfica para obtener datos de orígenes y un editor para aplicar
transformaciones. Debido a que el motor está disponible en muchos productos y
servicios, el destino en el que se almacenarán los datos depende de dónde se usó PQ.
Con esta herramienta, puede realizar el procesamiento de extracción, transformación y
carga (ETL)1 de datos. Su principal objetivo son los datos externos, pero se puede utilizar
esta herramienta incluso con datos de origen que estén en el documento en el que se va
a realizar el análisis, datos que pueden estar repartidos en varias hojas o datos que
pueden estar con formatos extraños.

1.1.1 Las cuatro fases de Power Query.

1.1.1.1 Conectar: Realiza conexiones a datos en la nube, en un servicio o localmente.

Se importa a un único origen de datos, como un libro de Excel, o a varias bases de

datos, fuentes o servicios repartidos por la nube. Los orígenes de datos incluyen datos
de la Web, archivos, bases de datos, Azure2 o incluso tablas de Excel en el libro actual.
Con PQ, se podrá combinar todos esos orígenes de datos con sus propias
transformaciones y combinaciones únicas para descubrir perspectivas que, de otro modo,
no se habría visto. Una vez importados los datos, se pueden actualizar para agregar
adiciones, cambios y eliminaciones del origen de datos externo.

1 Es el proceso donde se ponen a disposición los datos extraídos de múltiples fuentes, se limpian y/o
transforman en datos útiles, los cuales se cargan en diferentes plataformas.
2 Gran colección de servidores y hardware de red que ejecuta un conjunto complejo de aplicaciones

distribuidas
9
Capítulo 1. Introducción.

Imagen 1.1: Fase de Conectar. [Fuente: Elaboración propia, 2022]

1.1.1.2 Transformar: Da forma a los datos para satisfacer sus necesidades, mientras que
el origen permanece inalterado.

Transformar datos significa modificarlos de alguna manera para cumplir con los
requisitos de análisis de datos. Se puede quitar una columna, cambiar un tipo de datos o
filtrar filas. Cada una de estas operaciones es una transformación de datos. Este proceso
de aplicar transformaciones (y combinar) a uno o varios conjuntos de datos también se
denomina modelo de datos3.

PQ utiliza una ventana dedicada denominada Editor de Power Query para facilitar y
mostrar transformaciones de datos. El editor realiza un seguimiento de todo lo que hace
con los datos, registrando y etiquetando cada transformación o paso que aplique a los
datos. Si la transformación es una conexión de datos, una eliminación de columna, una
combinación o un cambio de tipo de datos, se puede ver y modificar cada transformación.

Hay muchas transformaciones que se pueden realizar desde la interfaz de usuario.

Cada transformación se registra como un paso en segundo plano. Incluso se puede

3 Proceso de analizar y definir todos los diferentes datos que se recopilan y producen.
10
Capítulo 1. Introducción.

modificar y escribir sus propios pasos con el idioma Power Query M en el Editor
avanzado.

Todas las transformaciones que se apliquen a las conexiones de datos constituyen

colectivamente una consulta, que es una nueva representación del origen de datos
original (y sin cambios). Al actualizar una consulta, cada paso se ejecuta
automáticamente. Las consultas reemplazan la necesidad de conectarse manualmente y
dar forma a los datos en Excel.

Imagen 1.2: Fase de Transformar. [Fuente: Elaboración propia, 2022]

1.1.1.3 Combinar: Integra datos de varios orígenes para obtener una vista única en los
datos.

En esta etapa, se combinar varias consultas del libro de Excel anexándolas o

fusionándolas. Las operaciones Anexar y Combinar se realizan en cualquier consulta con
una forma tabular y son independientes de los orígenes de datos de los que proceden los
datos.

Anexar. Una operación de datos anexados crea una nueva consulta que contiene
todas las filas de una primera consulta seguidas de todas las filas de una segunda
consulta. Se pueden realizar dos tipos de operaciones de datos anexados:

11
Capítulo 1. Introducción.

• Datos anexados intermedios: Crea una nueva consulta para cada operación de
datos anexados.
• Datos anexados en línea: Anexa datos a la consulta existente hasta que llega a un
resultado final.

Imagen 1.3: Anexar. [Fuente: Microsoft, 2022]

Combinar. Una operación de combinación crea una nueva consulta a partir de dos
consultas existentes. Esta consulta contiene todas las columnas de una tabla
principal, con una columna que actúa como vínculo de navegación a una tabla
relacionada. La tabla relacionada contiene todas las filas que coinciden con cada fila
de un valor de columna común en la tabla principal. Además, puede expandir o
agregar columnas de una tabla relacionada a una tabla principal.

Imagen 1.4: Combinar. [Fuente: Microsoft, 2022]

12
Capítulo 1. Introducción.

1.1.1.4 Carga: Completa la consulta y la carga en una hoja de cálculo o un modelo de

datos para luego actualizarse periódicamente.

Hay dos formas principales de cargar consultas en el libro:

• En el Editor de Power Query, se puede usar los comandos Cerrar y Cargar del
grupo Cerrar de la pestaña Inicio.
• En el panel Consultas del libro Excel, en Cargar en.

Otra manera es, ajustar las opciones de carga mediante el cuadro de diálogo para
seleccionar cómo se desea ver los datos y dónde se desea cargarlos, ya sea en una hoja
de cálculo o en un modelo de datos.

1.2 Introducción a Power Pivot.

Power Pivot (PP) permite realizar un análisis de datos eficaz y crear modelos de datos
sofisticados. Con PP se podrá combinar grandes volúmenes de datos de diversos
orígenes, realizar análisis de la información rápidamente y compartir puntos de vista con
facilidad. Se puede usar el modelo de datos y una colección de tablas para crear
relaciones almacenadas como parte del libro. El modelo de datos se integra
profundamente con otras características de Excel, como tablas y tablas dinámicas, para
proporcionar una experiencia de análisis perfecta.

Imagen 1.5: Barra Power Pivot. [Fuente: Elaboración propia, 2022]

13
Capítulo 1. Introducción.

1.2.1 Características principales de Power Pivot.

1.- Importar millones de filas de datos de varios orígenes de datos.

PP, importa millones de filas de datos de varios orígenes de datos en un único libro de
Excel, crea relaciones entre datos heterogéneos, crea columnas y medidas calculadas
con fórmulas, crea tablas dinámicas y gráficos dinámicos y, después, analiza los datos
para que se tomen decisiones, todo ello sin requerir asistencia de TI4.

2.- Disfrutar de cálculos y análisis rápidos.

Procesar millones de filas aproximadamente al mismo tiempo, así se genera un
procesamiento más rápido de los cálculos. Supera las limitaciones existentes para el
análisis masivo de datos en el escritorio con algoritmos de compresión eficientes para
cargar incluso los conjuntos de datos más grandes en la memoria.

3.- Soporte virtualmente ilimitado de orígenes de datos.

Proporciona la base para importar y combinar datos de origen desde cualquier ubicación
para realizar análisis masivos de datos en el escritorio, incluidas bases de datos
relacionales, orígenes multidimensionales, servicios en la nube, fuentes de datos,
archivos Excel, archivos de texto y datos de la Web.

1.2.2 Como se almacenan los datos.

Los datos con los que se trabaja en la ventana de PP y en Excel se almacenan en

una base de datos analítica dentro del libro de Excel; un eficaz motor local carga consulta
y actualiza los datos de dicha base de datos. Dado que los datos están en Excel, están
inmediatamente disponibles para las tablas dinámicas, los gráficos dinámicos y otras
características de Excel usadas para agregar datos e interactuar con ellos. Excel
proporciona todas las funciones de presentación e interactividad con los datos; los datos
y los objetos de presentación de Excel se encuentran en el mismo archivo de libro.

4 Comprende sistemas de información básicos basados en computadoras, que incluyen hardware,

software, sistemas operativos (SO) y los datos que se procesan para producir información útil.
14
Capítulo 1. Introducción.

1.3 Introducción a DAX.

DAX es una recopilación de funciones, operadores y constantes que se pueden usar

en una fórmula o expresión para calcular y devolver uno o varios valores. Dicho más
fácilmente, DAX ayuda a crear información de datos nueva que ya está en un modelo.

Puede que ya se esté familiarizado con la creación de fórmulas de Microsoft Excel.

Ese conocimiento será útil para comprender DAX, pero incluso si no se tiene ninguna
experiencia con las fórmulas de Excel, los conceptos que se van a describir, ayudarán a
empezar a crear fórmulas y a solucionar los problemas inmediatamente.

1.3.1 Características principales de DAX.

Existen tres conceptos fundamentales muy importantes de DAX: sintaxis, funciones

y contexto. Por supuesto, hay otros conceptos importantes en DAX, pero conocer estos
tres le proporcionará el mejor fundamento sobre el que basar sus conocimientos de DAX.

1. Sintaxis.

La sintaxis incluye varios elementos que conforman una fórmula, dicho en otras
palabras, cómo se escribe. Al tratar de comprender una fórmula DAX, a menudo resulta
útil descomponer cada uno de los elementos en un lenguaje común.

2. Funciones.

Las funciones son fórmulas predefinidas que realizan cálculos por medio de valores
específicos, denominados argumentos, en un orden o estructura determinados. Los
argumentos pueden ser otras funciones, otra fórmula, una expresión, referencias de
columna, números, texto, valores lógicos como TRUE o FALSE, o constantes.

DAX incluye las siguientes categorías de funciones: Fecha y hora, Inteligencia de

tiempo, Información, Lógicas, Matemáticas, Estadísticas, Texto, Primarias/Secundarias
15
Capítulo 1. Introducción.

y Otras. Si está familiarizado con las funciones en las fórmulas de Excel, muchas de las
funciones DAX le parecerán semejantes; sin embargo, las funciones DAX son únicas.

3. Contexto.

El contexto es uno de los conceptos de DAX más importantes. Hay dos tipos de
contexto en DAX: contexto de fila y contexto de filtro.

- Contexto de fila: Es más fácil pensar en el contexto de fila como la fila actual. Se
aplica siempre que una fórmula tiene una función use filtros para identificar una
fila individual en una tabla.

- Contexto de filtro: El contexto de filtro es un poco más difícil de entender que el

contexto de fila. Para simplificarlo, piense en el contexto de filtro como uno o varios
filtros aplicados en un cálculo que determina un resultado o valor.

16
Capítulo 2. Power Query, Power Pivot y DAX.

Capítulo 2. Power Query, Power Pivot y DAX.

2.1 Primeros pasos Power Query.

2.1.1 Identificación de las diferentes partes de PQ.

El primer paso para poder usar PQ es disponer de él. A partir de la versión 2016 de
Excel ya viene instalado en la ficha Datos, de la barra de opciones, en el grupo Obtener
y transformar datos. Desde allí se puede comenzar un proceso ETL con PQ. Figura 2.1

Imagen 2.1: Power Query 2016 y 365. [Fuente: Elaboración propia, 2022]

Es en Excel 2010 y 2013 se debe descargar como complemento gratuito. Debemos

acceder a su página web oficial, la elección debe ir en concordancia con la de Excel. Una
vez habilitada la función de PQ, se visualiza en la barra de opciones.

Imagen 2.2: Power Query 2010 y 2013. [Fuente: Microsoft 2022]

17
Capítulo 2. Power Query, Power Pivot y DAX.

2.1.2 Transformaciones básicas de PQ.

En la ventana del editor de PQ no provocamos el cambio de ningún dato en el origen de

datos. Todas las transformaciones que se realicen se harán sobre los datos que se
importan. Los orígenes de datos seguirán sin alterarse, siguen a salvo por decirlo de
alguna manera. Aquí estaremos trabajando con datos que están en otro sitio pero que
esos datos no se van a modificar de ninguna manera. Las partes más importantes del
editor son las mencionadas en la Figura 2.3.

Imagen 2.3: Partes del Editor PQ. [Fuente: Microsoft 2022]

1.- En la parte superior de la ventada del Editor de PQ se encuentra la cinta de opciones

en las que principalmente están todas las herramientas con las que podemos transformar
la información, procesar los datos que nos han llegado.

- Inicio: se encuentran las opciones más habituales

- Transformar: aquí tenemos todas las transformaciones que podemos realizar sobre los
datos que se ven aquí.
- Agregar columnas: todo lo relacionado con crear nuevas columnas de datos.

18
Capítulo 2. Power Query, Power Pivot y DAX.

2.- En este apartado se ven todas las consultas que tengamos en el documento
que estamos trabajando.

3.- Panel llamado configuración de la consulta. En el podemos cambiar algunas

propiedades de la consulta, por ejemplo, su nombre y también podemos ver los pasos
que se van realizando. Esta zona es muy importante porque entre otras cosas nos va a
permitir eliminar pasos, modificarlos, re ordenarlos. Este es uno de los conceptos más
interesantes de PQ el que se guarda en los pasos que vamos realizando sobre los datos,
eso nos va a permitir reutilizarlos, nos permitirá automatizar los pasos de importación y
transformación de los datos sin necesidad de, por ejemplo, utilizar otros caminos más
complicados que se utilizaban antes como macros. Al guardarse todos los pasos en la
consulta luego será muy fácil repetirlos, que suele ser algo muy habitual cuando se
trabaja con datos externos.

4.- La mayor parte de la ventana es la vista previa, aquí veremos cómo van
quedando los datos con las transformaciones que se van realizando, es una vista de solo
lectura, aquí no podemos hacer doble clic ni intentar modificar uno de los datos de forma
manual. Las transformaciones se realizan con las opciones que se encuentran en el punto
1. Dependiendo de lo que se haga, en la vista previa se verá reflejado el resultado para
ver cómo queda.

5.- Debajo de la cinta de opciones esta la barra la cual es muy similar a la barra
de Excel, de hecho, aquí también tenemos algo que podría considerarse a una formula,
pero no de Excel si no de PW. Es una fórmula que esta creada con algo similar a un
lenguaje de programación llamado M. La mayor parte del trabajo en el editor de consultas
se realiza a través de las opciones que están en el punto 1. Cuando utilicemos alguna
opción el editor de consultas las traducirá al código M correspondiente, es decir, nosotros
no lo tenemos que escribir, el editor lo hace de manera automática.

6.- En el panel de detalles si seleccionamos una fila o celda de la tabla de

resultados se mostrarán sus detalles. Cada columna se mostrará como una fila.

19
Capítulo 2. Power Query, Power Pivot y DAX.

7.- Debajo de la vista previa, encontramos la barra de estado. Nos informa

cuantas columnas y filas se han cargado.

2.2 Power Pivot.

2.2.1 Identificación de las diferentes partes de PP.

Como todas las ventanas en su parte posterior se encuentra la cinta de opciones donde
están todas las operaciones.

Imagen 2.4: Partes de Power Pivot. [Fuente: Microsoft, 2022]

1.- En la pestaña inicio están las opciones más usadas, donde se podrá añadir datos al
modelo de datos desde una tabla Excel o desde otros orígenes de datos. La opción
actualizar realiza la actualización del modelo o de alguna de sus tablas.

2.- En la pestaña diseñar se puede encontrar la opción crear las relaciones de los
modelos de datos.

3.- En versiones anteriores de Excel 2010 o 2013 se visualiza otra pestaña llamada Tabla
vinculada donde está relacionada a los orígenes de datos que se han ingresado en el
modelo. En las nuevas versiones de Excel ha desaparecido.

4.- En la barra de fórmulas que se encuentra debajo de la cinta de opciones, se pueden

escribir formulas DAX
20
Capítulo 2. Power Query, Power Pivot y DAX.

5.- Los filtros que se aplican en PP no son los mismo que en PQ, ya que estos solo limitan
la información que se vería y no la que se va a utilizar en una tabla dinámica.

6.- La parte central está dedicada a los datos, es decir las tablas que forman el modelo
de datos. Al contrario de Power Query este apartado no es una vista previa, es el modelo
de datos tal cual.

7.- En la barra inferior se indica cuantas filas de datos tiene cada una de las tablas en
cada hoja.

2.2.2 Transformaciones básicas de PP.

En la versión de Excel 2016 o Excel 365 ya está integrada la herramienta de PP, no

hay que instalar complemento, pero no todas las versiones de Excel 2016 lo tienen. En
Excel 365 todas las versiones traen incorporado PP. Se vera una pestaña con el mismo
nombre.

Imagen 2.5: Power Pivot. [Fuente: Microsoft, 2022]

Cuando se selecciona Administrar, aparece la ventana de PP, que es donde se

puede ver y administrar el modelo de datos, agregar cálculos, establecer relaciones y ver
los elementos del modelo de datos de PP. Un modelo de datos es una colección de tablas
o de cualquier otro dato, generalmente con relaciones establecidas entre ellos. La
siguiente imagen muestra la ventana de Power Pivot con una tabla mostrada.

21
Capítulo 2. Power Query, Power Pivot y DAX.

Imagen 2.6: Ventana Power Pivot. [Fuente: Microsoft, 2022]

La ventana de PP también puede establecer (y representar gráficamente)

relaciones entre los datos incluidos en el modelo. Al seleccionar el icono Vista de
diagrama de la esquina inferior derecha de la ventana, puede ver las relaciones
existentes en el modelo de datos de PP.

Imagen 2.7: Vista de diagrama. [Fuente: Microsoft, 2022]

22
Capítulo 2. Power Query, Power Pivot y DAX.

2.3 DAX.

DAX (Data Analysis Expressions) es un lenguaje específico para análisis de datos

creado por Microsoft en el año 2010 para ser usado con un modelo de datos tabular y
que se puede usar en Excel, Analysis Services y Power BI

2.3.1 Principales características de DAX.

a) Modelo de Datos Tabular

Un modelo de datos tabular no es más que un conjunto de tablas formadas por filas
divididas en columnas y relacionadas entre sí.

Cada tabla está dividida en columnas con un nombre y algunas de ellas pueden
contener expresiones escritas en DAX, por ejemplo, para hacer un cálculo usando valores
de otras columnas. Cuando los datos se importan en el modelo, se crean filas en cada
tabla.

Una relación enlaza dos tablas usando una columna de cada tabla. Las expresiones
DAX utilizan las relaciones definidas en el modelo para, por ejemplo, saber cómo filtrar
los datos.

b) Tipos de datos
DAX define varios tipos de datos y una columna sólo puede contener datos de un
mismo tipo.

Los principales tipos de datos son:

• Número entero
• Número decimal
• Cadena de texto
• Fecha
• Moneda
23
Capítulo 2. Power Query, Power Pivot y DAX.

• Boolean

c) Columnas calculadas
Las columnas calculadas son un tipo especial de columna que contienen una
expresión DAX que generalmente usa datos de otras columnas para devolver un
resultado, por ejemplo, una columna PrecioTotal que utilice las columnas PrecioUnidad y
Cantidad.

Este cálculo se realiza cuando se importan los datos al modelo y el resultado se

almacena en cada fila del modelo.

d) Medidas
Las medidas son otro tipo de columna especial que también contienen una
expresión DAX, pero el cálculo sólo se realiza cuando la columna es utilizada en un
reporte, y no se almacena en el modelo. Generalmente agregan datos de varias filas de
la tabla.

Las medidas son globales, aunque se definan para una tabla, por lo que los nombres
tienen que ser únicos en todo el modelo.

e) Relaciones
Para definir una relación entre dos tablas, una de ellas tiene que tener una columna
con valores que no se repitan y que sería la columna llave de la tabla y la otra debe tener
una columna del mismo tipo y conteniendo los mismos valores pero que pueden estar
repetidos. Esto es lo que se llama una relación de uno a muchos, porque por cada fila de
una tabla pueden existir muchas filas de la tabla relacionada.

La relación puede ser en una sola dirección, siempre desde el lado uno hacia el
mucho. O puede ser en ambas direcciones. Las expresiones DAX tienen en cuanta la
dirección de la relación cuando aplican filtros.

24
Capítulo 2. Power Query, Power Pivot y DAX.

f) Funciones
DAX es un lenguaje que utiliza funciones a las que se les pasa parámetros y que
devuelven un valor de un tipo determinado. Los parámetros pueden ser a su vez
llamadas a otras funciones.

El tipo de datos Tabla, puede usarse en las funciones como resultado o como
parámetro, pero no puede usarse en las columnas.

g) Expresiones
Para crear las columnas calculadas y las medidas usamos expresiones DAX, que
pueden contener operadores y llamadas a funciones.

Para referirse a una columna se utiliza el nombre de la tabla seguido del nombre de
la columna entre corchetes, por ejemplo, Producto[Precio].

2.3.2 Algunas funciones básicas de DAX.

Las funciones se pudieran agrupar de la siguiente manera:

• Fecha y hora
• Inteligencia de tiempo
• Filtros
• Información
• Lógicas
• Matemáticas
• Estadísticas
• Textos

Algunas de las funciones más principales son:

25
Capítulo 2. Power Query, Power Pivot y DAX.

SUM(columna)

Es una función de agregación que suma todos los valores de una columna en una tabla.
Hay otras funciones de agregación: AVERAGE, MIN, MAX, STDEV, VAR.

SUMX(tabla, expresión)

Itera sobre cada fila de una tabla, calculando la expresión y sumando el resultado.

COUNT(columna)

Cuenta el número de elementos en una columna que contienen números.

COUNTROWS(tabla)

Cuenta el número de filas de una tabla.

FILTER(tabla, condición)

Devuelve una tabla con las filas de la tabla original que cumplan con la condición
expresada en el segundo parámetro.

DATESYTD(columna)

Devuelve una tabla con los del año en curso hasta la fecha actual, presentes en la
columna pasada como parámetro. La columna tiene que ser de tipo Fecha.

2.3.3 Algunas expresiones básicas de DAX.

Producto[Sub Total] = Producto[Precio Unidad] * Producto[Cantidad Unidades]

Esta expresión crea una columna calculada a partir de otras dos columnas de la misma
tabla.
26
Capítulo 2. Power Query, Power Pivot y DAX.

Producto[Precio Total] := SUM(Producto[Sub Total])

Esta expresión crea una medida que calcula el precio total para los productos
seleccionados. En ella hemos usado la función SUM y la columna calculada del ejemplo
anterior.

Producto[Precio Total] := SUMX(

Producto,

Producto[Precio Unidad] * Producto[Cantidad Unidades]

Esta expresión crea la misma medida del ejemplo anterior, pero utilizando la función
SUMX y sin utilizar la columna calculada Producto[Sub Total].

Producto[Precio Total Verdes] := SUMX(

FILTER(

Producto,

Producto[Color] = "Verde"

),

Producto[Precio Unidad] * Producto[Cantidad Unidades]

Esta expresión crea una medida que calcula el precio total para los productos de color
verde. Hemos usado la función FILTER como primer parámetro de la función SUMX.

27
Capítulo 3. Implementación.

Capítulo 3. Implementación.

En este capítulo se implementarán las herramientas aprendidas para simplificar

todo el análisis de las planillas con la información relacionada con ventas de
productos, teniendo como objetivo mostrar de una manera más simple el análisis
utilizando las herramientas de Power Query, Power Pivot y DAX.

3.1 Instalación de herramientas.

Antes que todo, se debe realizar la instalación de Microsoft Office para poder optar
a las herramientas que trae consigo. Para realizarlo es necesario comprar la licencia
la cual se puede obtener desde su página web oficial. Se recomienda descargar la
versión más reciente, debido a que estas son las que tienen disponibles las
herramientas que se van a utilizar. Cabe destacar que solo están disponibles para
Windows, no hay versión de estas herramientas en Excel para Mac.

Imagen 3.1: Disponibilidad de herramientas. [Fuente: Elaboración propia, 2022]

3.2 Creación de base de datos.

Con la configuración ya terminada, se puede comenzar con el proceso ETL en

Power Query. Lo primero es realizar todas las transformaciones a las tablas para
dejarlas lo más pulcro posible el documento. Como se muestra en las figuras:

28
Capítulo 3. Implementación.

Imagen 3.2: Documento sin proceso ETL. [Fuente: Elaboración propia, 2022]

Imagen 3.3: Documento con proceso ETL. [Fuente: Elaboración propia, 2022]

Las otras tablas que deben pasar por el proceso ETL son: Centros, Productos,
Categorías y Ventas. Estos documentos externos se van a utilizar en el Modelo de
Datos.

Lo siguiente es entrar a Power Pivot y desde ahí escoger la opción que permite
cargar documentos de Excel, le damos nombre a la conexión y se da búsqueda al
documento. Se cargarán las 4 talas creadas en el Modelo de Datos como muestra en
la Imagen 3.4.

29
Capítulo 3. Implementación.

Imagen 3.4: Tablas cargadas al Modelo de Datos. [Fuente: Elaboración propia, 2022]

Teniendo las tablas cargadas en el Modelo de Datos, el siguiente paso ya es el de

crear las relaciones. Imagen 3.5.

Imagen 3.5: Modelo base de datos. [Fuente: Elaboración propia, 2022]

Para mejor entendimiento de este modelo de base de datos se procederá a explicar

sus relaciones:
30
Capítulo 3. Implementación.

• Una categoría puede tener diferentes productos.

• Pueden existir diferentes centros.
• Una venta puede tener varios productos.

Vamos a procesar en DAX un documento con más tablas incorporadas al Modelo de

Datos donde ya están relacionadas.

Cuando ya está creada la lógica de la base de datos se procede a realizar las

operaciones en DAX para obtener los totales de ventas por subcategorías y diferentes
totales distribuidos por varios criterios.

a) Creación de la tabla dinámica y la medida del total de ventas.

Esta etapa comprende la creación de la tabla dinámica en la que se van a organizar

la información por subcategorías, estás serán las filas.

La primera estadística que se debe calcular es el total, es sumar la columna del

“Importe de las Operaciones”. Se debe crear en la tabla de las “Operaciones” la
medida “$ Ventas” cuya formula es “=SUM(Operaciones[ImporteOperacion])” y se
modifica el tipo y formato del resultado.

Una vez activada la medida se verá como la siguiente imagen:

Imagen 3.6: Medida “$ Ventas”. [Fuente: Elaboración propia, 2022]

31
Capítulo 3. Implementación.

b) Creación medida del cálculo total para los hombres y mujeres.

En esta medida se utilizará la función “CALCULATE” ya que se necesita obtener los

totales de las ventas, pero solo de los clientes que son hombres. En esta medida se
crea un tipo de filtro para que la fórmula que se crea se utilicen solo las filas de datos
de los hombres. Es por ello que se utilizará “CALCULATE” ya que permitirá modificar
el contexto de filtro, las condiciones de filtro de la formula en la que aparece la función.

Se crea la nueva medida “$ Ventas Hombres” cuya formula es “=CALCULATE([$

Ventas]; Operaciones[SexoCliente] = "H")”.

El código antes mencionado contiene un argumento obligatorio y a partir de ese

argumento existen argumentos opcionales los cuales son filtros que podemos indicar.

Lo primero es la expresión que se quiere calcular, el cálculo que se debe realizar para
obtener los resultados. En este caso como se quiere seguir obteniendo el total de las
ventas se utiliza la isma expresión que la medida anterior, es decir usa la función SUM
para volver a obtener el Importe total de la tabla Operaciones, pero como ese dato ya
se ha calculado en una medida se puede aprovechar esa medida, “$ Ventas” y así ya
se ha indicado cual es la expresión, cual es el cálculo que se debe realizar.

En el segundo argumento es donde se indica los filtros que modificaran el contexto de

filtro y para este caso, lo que se está haciendo es crear un filtro simple que se añade
al contexto de filtro de la formula, del cálculo.

Se dice que es un filtro simple porque afecta a una sola columna. Esta es la opción
más sencilla para modificar el contexto de filtro a través de CALCULATE. Aplicar filtros
a columnas individuales.

32
Capítulo 3. Implementación.

En concreto en este filtro, se indicó que se usaran para realizar el cálculo de la

expresión que se ha indicado en el primer argumento, deben cumplir en la columna
del “SexoCliente” el valor que sea igual a una “H” que sería el caso de los hombres

Así se consigue que en cada uno de los resultados que se obtendrán en el contexto
de filtro, también se tengan en cuenta la condición que se está añadiendo. Con esto
se está consiguiendo que el contexto que se tiene por la configuración de la tabla
dinámica que se ha creado, se le añada este nuevo filtro. En otras palabras, esto
quiere decir que en la formula se van a usar las filas de datos que corresponden a
cada una de las Subcategorías, pero solo los datos de los hombres.

Se añade un nuevo filtro modificando el contexto, para que en esa fórmula se usen
solo los datos que cumplen las dos cosas, que sean de la subcategoría que toca y que
sean solo los datos de hombres.

Para la medida del cálculo de ventas de mujeres se utiliza la misma medida del cálculo
de ventas de hombres solamente modificado la letra “H” por la letra “M”.

Imagen 3.7: Medida Ventas Hombres y Mujeres. [Fuente: Elaboración propia, 2022]

c) Creación de medida para el cálculo de los rangos de edades.

Se crea la nueva medida “$ Ventas hasta 30 años” cuya formula es “=CALCULATE([$

Ventas]; Operaciones[EdadCliente] <=30)”

33
Capítulo 3. Implementación.

En este caso la única diferencia con las fórmulas anteriores es que se usa un operador
de comparación diferente, el menor o igual para indicar que se use los datos de las
personas que tienen hasta 30 años. Como en este caso se trata de un dato numérico,
lo que se utiliza para comparar no se ha puesto en comillas dobles. Se estable el tipo
de dato y se añade la medida.

El siguiente rango de edades es el que va de los 31 años a los 45 años. En este caso
tenemos que pensar en algo diferente porque los típicos operadores de mayor o
menor, por sí solos no valen y, además, no hay un operador “entre”. En estos casos
se puede optar por simularlo, controlando dos condiciones: que sea mayor o igual a
31 y que también sea menor o igual a 45 años. Es otra manera de aplicar un operador
“entre”.

Para conseguir simular el “entre”, se necesita indicar en el filtro de la función

CALCULATE dos condiciones:

La primera por ejemplo con la función AND.

Se crea la medida “$ Ventas entre 31 y 45 años” y escribimos la siguiente fórmula:

“=CALCULATE([$ Ventas]; AND(Operaciones[EdadCliente] >=31;
Operaciones[EdadCliente] <=45))”

La función AND de DAX es similar a la de Excel en su interior hay que controlar las
condiciones que se requieren que se cumplan. En este caso las dos que se han
indicado, que la edad sea mayor o igual a 31 años y que la edad sea menor o igual a
45 años, cada condición se indica en un argumento de la función AND y se deben
cumplir las dos para que se considere en su conjunto verdadera. Las filas de datos
que cumplan las dos cosas son las que se usarán en el cálculo.

34
Capítulo 3. Implementación.

Sobre la función AND, existe un problema y este es que está limitado en cuanto al
número de condiciones que se pueden controlar, sólo se pueden poner dos. En el
AND de Excel no es así, se pueden poner más.

Vamos a crear una nueva medida llamada “$ Ventas entre 46 y 60 años” la cual tendrá
la siguiente formula: “=CALCULATE([$ Ventas]; Operaciones[EdadCliente] >=46 &&
Operaciones[EdadCliente] <=60)”.

Para utilizar el AND también se pueden utilizar estos caracteres “&&” que
representarían al operador AND, colocándolos entre dos condiciones se deben
cumplir ambas para que la condición sea considerada como verdadera.

La ventaja que tiene hacerlo así es que de esta manera sí se pueden indicar más
condiciones. Si por ejemplo habría una tercera condición a controlar simplemente
tendríamos que poner al final de la formula los caracteres && y a continuación escribir
la nueva condición que también se quiere que se cumpla.

Finalmente se crea la medida con los datos de los clientes mayores de 60 años.

Para esto creamos la medida “$ Ventas Mayores de 60 años” la cual tendrá la

siguiente formula: “=CALCULATE([$ Ventas]; Operaciones[EdadCliente] > 60)”

Si ahora seleccionamos los 4 rangos de edades relacionados con la edad, debiera

coincidir Imagen 3.8

35
Capítulo 3. Implementación.

Imagen 3.8: Creación de medidas por rango de edades. [Fuente: Elaboración propia, 2022]

d) Eliminar los datos en negativo

En la tabla Operaciones no hay sólo datos de ventas, sino que también hay datos de
devoluciones, ya que los datos están mezclados.

La única diferencia es que las devoluciones están en negativo y las ventas en positivo.
Esto significa que los cálculos que se han realizado hasta el momento, están mal.
Porque los datos de las devoluciones también se están usando y al estar en negativo
se están restando, entonces lo que se obtuvo fue una especie de resultado neto.

Para que solo se usen los importes de ventas y que no se tengan en cuenta esas filas
de datos de devoluciones se debe añadir otra condición al contexto de filtro.

En concreto lo que se debe hacer, es decirle que en el cálculo solo se debe usar las
filas de datos que el importe lo tengan en positivo, es decir mayor que cero y así de
esa manera ya no se usarán las filas de datos con importes negativos los de las
devoluciones.

Pero nos encontramos que en los filtros simples no se permite crear condiciones
complejas en el sentido de usar varias columnas diferentes por ejemplo en las edades
no hubo problema porque para ambas condiciones se usaba la columna de la edad,
se controlaba si era mayor o igual a 31 años y menor o igual a 45 años, pero se hacía
con la misma columna en ambos casos la edad.
36
Capítulo 3. Implementación.

Pero en este caso se estaría utilizando dos columnas, la del sexo cliente y la del
importe. Esto no se permite son columnas diferentes. Imagen 3.9

Imagen 3.9: Columnas SexoCliente e ImporteOperacion. [Fuente: Elaboración propia, 2022]

Lo que se ha creado hasta el momento son filtros simples. Se les llama así porque
nos referimos a columnas para crear las condiciones. Sólo se puede utilizar una
columna en cada expresión que debe ser evaluada para filtrar, es decir en el segundo
argumento calculado en la función “$ Ventas Hombres” no se puede referir a dos
columnas diferentes para crear un filtro.

Es decir, en el CACULATE, lo que no se puede hacer es usar en un solo argumento

de filtro varias columnas, pero sí que se puede crear diferentes argumentos de filtros
para que funcionen en conjunto. En cada uno de ellos se controlará una columna.

Por lo tanto, añadiendo la condición que permite controlar los importes mayor que 0,
la formula quedara de la siguiente manera: “=CALCULATE([$ Ventas];
Operaciones[SexoCliente] = "H"; Operaciones[ImporteOperacion] > 0)

No se podía crear en una función, una condición que controlar a dos columnas, pero
sí se puede crear diferentes argumentos de filtros que en conjunto funcionan como un
&&. En el segundo argumento que es el primero de tipo filtro se ha indicado la primera
condición y en el otro argumento el importe de la operación.

Los resultados varían ya que ahora solo se está utilizando datos de filas de ventas.
Los datos de devoluciones se han dejado de utilizar.

Por tanto, las demás medidas estarían erróneas ya que todas fueron calculadas con
el valor neto. De restar a las ventas las devoluciones se tendría que ir cambiando
37
Capítulo 3. Implementación.

todos los cálculos para obtener los totales reales de las ventas y no los que tienen en
cuenta las devoluciones. Es decir, se debería ir añadiendo en todos los cálculos en
todas las medidas ese segundo filtro que se ha añadido en el caso de los hombres.

Pero recordemos que las medidas se pueden reutilizar y que de hecho todas las
medidas que se han realizado hasta aquí de resultados condicionales, han reutilizado
la primera medida “$ Ventas”.

Lo cual, si se añade el filtro que se acaba de crear a la primera de todas, a la que en

teoría era de las “Ventas”, se tendría corregido el problema porque si en esa medida
se filtra para que sólo se usen los datos de las ventas, en el resto de medidas ese filtro
ya estará aplicado. A esas medidas ya llegarán solo los datos que se corresponden
con las ventas.

Finalmente se dejará la medida “$ Ventas Hombres” como estaba originalmente y

reutilizaremos la medida “$ Ventas”

En la primera medida debemos generar un cambio ya que ahora sabemos que la tabla
de las Operaciones hay datos mezclados, cambiaremos la fórmula para que con una
función CALCULATE se usen solo las filas de ventas, es decir que tengan el Importe
mayor que 0. Lo cual la formula queda de la siguiente manera:
“=CALCULATE(SUM(Operaciones[ImporteOperacion]); Operaciones[ImporteOperacion]
>0)”.

Indicando el filtro en color rojo en la medida, ya se está condicionando los datos que
se usaran en las otras medidas.

Como punto final podemos agregar una segmentación de datos el cual nos facilita
enormemente al momento de realizar los análisis. Este puede ser de año, mes, país,
etc.

38
Capítulo 4. Conclusiones.

Capítulo 4. Conclusiones.

Las conclusiones forman una parte importante del documento, en el cual se reflexiona
sobre todo lo aprendido durante el transcurso del proyecto.

El objetivo siempre fue lograr un mejor rendimiento en cuanto al análisis de la información

utilizando Power Query, Power Pivot y DAX. Se puede decir que el proyecto culmino de
manera satisfactoria, logrando abarcar todas las etapas que esto lo conlleva.

En el transcurso de la realización del proyecto, una de las complicaciones fue las

relaciones en la base de datos, ya que con el tiempo estos conocimientos se habían
perdido. Esto fue tomado como reto personal ya que este proyecto, no solo es para para
llegar a fin la etapa universitaria, sino que también un aporte a los estudiantes de la
universidad.

Estas herramientas son impresionantes, sobre todo por el tema de ver hasta qué punto
con las condiciones o filtros se pueden superar dificultades y obtener cálculos que antes
eran imposibles y por supuesto, por ver cómo la reutilización de medidas nos ha permitido
realizar un cambio en la medida de la que partían todas las demás y así ahorrar trabajo.

Finalmente, y como opinión personal, considero que esta experiencia fue totalmente
enriquecedora, ya que me permitió traspasar todo lo aprendido en los años que llevo en
mi puesto de trabajo.

39
Capítulo 5. Referencia bibliográfica.

Capítulo 5. Referencia bibliográfica.

Analisis de datos. (2022). Obtenido de

https://www.intel.es/content/www/es/es/analytics/what-is-data-analytics.html
Expresiones de análisis de datos (DAX) en PowerPivot. (Octubre de 2022). Obtenido
de https://support.microsoft.com/es-es/office/expresiones-de-an%C3%A1lisis-
de-datos-dax-en-powerpivot-bab3fbe3-2385-485a-980b-5f64d3b0f730
Power Pivot: Información general y aprendizaje. (Octubre de 2022). Obtenido de
https://support.microsoft.com/es-es/office/power-pivot-informaci%C3%B3n-
general-y-aprendizaje-f9001958-7901-4caa-ad80-028a6d2432ed
Power Query. (Abril de 2022). Obtenido de https://www.microsoft.com/es-cl/

40
Anexo.

Anexo.

Charla 1

41
Anexo.

42
Anexo.

rimeros pasos e introducción a

o er uery

ETL: Extract, Transform and Creación de columnas, filtrado

Load. (Extraer, transformar y m ltiples opciones de
y cargar). transformación.

osibilidad de acceder a ara qu repetir el proceso

m ltiples formatos de ETL sea tan fácil como hacer
orígenes de datos, incluso clic o elegir una opción.
almacenados en diferentes
sitios.

43
Anexo.

o er uery es capaz de conectarse a una gran variedad de

orígenes de datos, esta es una de sus características más
destacadas . uede conectarse a m ltiples formatos de archivos,
xlsx, csv, a archivos ML, a base de datos de Access a paginas
eb.

Une dos tablas con la misma estructura en una sola, obteniendo una
nueva tabla con las mismas columnas y con el total de filas de
ambas tablas

44
Anexo.

Implica unir columnas de dos tablas que tienen valores en com n por
otra columna.

45
Anexo.

Charla 2

46
Anexo.

rimeros pasos con los Modelos

de atos y o er ivot

47
Anexo.

arte importante que nos permite crear ase de atos elacionales .

Herramienta de creación y depuración de modelos de datos.

48
Anexo.

Afortunadamente para
nosotros, o er ívot te
permite importar data desde
distintas fuentes de data
directamente a Excel sin
tener que encontrarse con los
problemas como crear
m ltiples hojas de trabajo
para distintas fuentes de data

Identificar qu campos se deben relacionar.

Las columnas que se van a relacionar, los campos deben tener el
mismo tipo de dato.
Entender el funcionamiento de las tablas, buscar la lógica del
diseño.

49
Anexo.

Charla 3

50
Anexo.

51
Anexo.

Abreviatura de ata Las fórmulas y funciones

Analysis Expresions . clásicas de Excel están
(Expresiones de análisis de pensadas para trabajar a
datos) . nivel celda, A en
cambio, trabaja a nivel de
tablas y columnas .

uevo lenguaje de fórmulas

que contiene un nuevo set
de funciones y operadores.

52
Anexo.

53
Anexo.

Complementar lo estudiado con o er I ya que es la

union de todas las herramientas o er que tiene Excel en
una sola.

Es un servicio en la nube de Microsoft, en un entorno

conectado, y que es compartido a traves del servicio de
Microsoft

54
Anexo.

55