Excel Business Intelligence

• Power Query es una herramienta que permite importar, transformar y preparar datos.

• Se puede usar en Excel, donde se conoce como Obtener & transformar, y es una herramienta recomendada para importar datos.

• Access es una base de datos de Microsoft que es de uso personal, pero que a su vez es muy poderosa.

• Podemos fácilmente intercambiar datos de Access para su análisis en Excel.

Un Archivo en formato CSV tendrá los datos separados por comas (Comma-Separated-Values).

XML, o Lenguaje de Marcado Extensible (Extensible Markup Language), es un formato de archivo, basado en HTML, que permite almacenar, organizar y transmitir datos de forma estructurada.

• Desde un archivo de Excel podemos leer otros archivos de Excel.

• Con Power Query podemos leer los archivos y consolidarlos.

• Los archivos deben tener la misma estructura.

• Con Power Query podemos leer los archivos y consolidarlos de una página web.

• Power Query nos mostrará las diferentes tablas de la página.

• Con Power Query podemos leer los archivos y quitar, mover y filtrar columnas.

• Power Query nos mostrará las diferentes columnas de la página.

• Con Power Query podemos transformar e insertar columnas columnas.

• Power Query nos mostrará las diferentes columnas de la página.

• Con Power Query podemos combinar tablas relacionándolas por una columna (llave).

• La combinación la podemos realizar desde Excel.

• Con Power Query podemos Consolidar tablas de Excel.

• La combinación la podemos realizar desde Excel.

• Con Power Query podemos administrar las conexiones con los archivos.

• Podemos cambiar las propiedades de la conexión.

• Con Power Query podemos modificar el formato de las columnas como texto, numérico y fecha.

• Podemos cambiar las propiedades de la conexión.

• Con Power Query podemos Insertar nuevas columnas.

• Podemos cambiar las propiedades de la conexión.

• Con Power Query podemos Transponer información.

• Hay que invertir también los títulos.

• Con Power Query podemos cambiar la apariencia de nuestro escenario.

• Dependencia de las relaciones

• ¿Algo para mejorar en esta sección?

• ¿Algún comentario o aporte?

• Tus comentario son muy importantes y seguiré trabajando para que puedas calificar este curso con 5 estrellas.

• Power Pivot es un complemento gratuito de Excel para el análisis de datos y el modelado de datos.

• Es una herramienta de inteligencia de negocios (BI) que permite trabajar con grandes cantidades de datos de diversas fuentes y crear informes y gráficos para mostrar información relevante.

Para importar datos a Power Pivot:

• Abrir la ventana de Power Pivot en Excel, haciendo clic en la pestaña Power Pivot y luego en Administrar modelo de datos.

• Hacer clic en Inicio y luego en Obtener datos externos.

Seleccionar uno de los botones para abrir el Asistente para la importación de tablas.

Crear un modelo de datos

Para importar datos a Power Pivot:

• Abrir la ventana de Power Pivot en Excel, haciendo clic en la pestaña Power Pivot y luego en Administrar modelo de datos.

• Hacer clic en Inicio y luego en Obtener datos externos.

Seleccionar uno de los botones para abrir el Asistente para la importación de tablas.

Para importar datos a Power Pivot:

• Abrir la ventana de Power Pivot en Excel, haciendo clic en la pestaña Power Pivot y luego en Administrar modelo de datos.

• Hacer clic en Inicio y luego en Obtener datos externos.

Seleccionar uno de los botones para abrir el Asistente para la importación de tablas.

Para crear una tabla dinámica desde Power Pivot:

• Primero debemos crear nuestro modelo de datos

• En Inicio seleccionamos crear tabla dinámica.

Para crear una tabla dinámica desde Power Pivot:

• Primero debemos crear nuestro modelo de datos

• En Inicio seleccionamos crear gráfica dinámica.

Para crear una tabla dinámica desde Power Pivot:

• Primero debemos crear nuestro modelo de datos

• En Inicio seleccionamos crear gráfica dinámica.

Para crear una tabla dinámica desde Power Pivot:

• Primero debemos crear nuestro modelo de datos

• Podemos modificar el formato, el orden, el filtro y modificar las columnas.

• Power Pivot tiene una área de cálculo para datos estadísticos en general.

• DAX o Expresiones de Análisis de Datos, es un lenguaje de fórmulas que se utiliza para calcular y devolver valores en Power BI, Power Pivot para Excel y Analysis Services.

• DAX se contiene funciones, operadores y constantes que se pueden combinar para crear fórmulas y expresiones.

• Crear fórmulas de DAX para columnas calculadas y medidas.

• DAX y Excel tienen muchas funciones comunes, pero en DAX están en inglés.

• Las fórmulas y expresiones DAX no pueden modificar o insertar valores individuales en las tablas.

• No se pueden crear filas calculadas con DAX. Solo se pueden crear medidas y columnas calculadas.

• Al definir columnas calculadas, se pueden anidar funciones en cualquier nivel.

• DAX tiene varias funciones que devuelven una tabla. Normalmente, se usan los valores que devuelven estas funciones como entrada a otras funciones, que requieren una tabla como entrada.

• Los KPI (indicador clave de rendimiento) son medidas visuales muy intuitivas del rendimiento.

• Los KPI tienen diferentes símbolos y podemos elegir el que queramos.

• Se puede crear un KPI sólo de medida, no en las columnas calculadas.

• Al guardar un libro Excel con un modelo de datos creado, la extensión del archivo es XLSX,

• Protegemos un libro en: Revisar > Proteger libro

Power Map es una herramienta de Excel que permite visualizar datos en 3D, trazar datos geográficos y temporales, y compartir la información.

• Una jerarquía aparece como un único objeto en la lista de campos.

• Las jerarquías facilitan a los usuarios seleccionar y navegar por rutas comunes de datos al crear informes y tablas dinámicas.

• Las perspectivas crean vistas personalizadas que se definen para un escenario de usuarios determinado, lo que facilita la navegación por grandes conjuntos de datos.

• Puede incluir cualquier combinación de tablas, columnas y medidas (incluidos los KPI) en una perspectiva, y puede crear varias perspectivas para varias circunscripciones jerárquicas de su organización.

• ¿Algo para mejorar en esta sección?

• ¿Algún comentario o aporte?

• Tus comentario son muy importantes y seguiré trabajando para que puedas calificar este curso con 5 estrellas.

• DAX o Expresiones de Análisis de Datos.

• 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.

El modelo tabular en Power Pivot es una base de datos que ofrece acceso rápido desde las tablas y gráficos dinámicos de Excel a los datos y objetos almacenados en Power Pivot.

• Hacer una tabla para las medidas. No tiene relación.

• La sintaxis del lenguaje DAX hereda muchos elementos de la sintaxis de Excel.

• Desde la ventana Power Pivot podemos crear columnas calculadas y medidas con DAX.

• Devuelve la suma de una expresión evaluada para cada fila de una tabla.

• SUMX(<table>, <expression>)

• La función SUMX toma como primer argumento una tabla, o bien una expresión que devuelve una tabla.

Una buena práctica es crear una tabla independiente para las medidas y no realizar ninguna vinculación.

Para definir los nombres de tabla hay que tener en cuenta que son únicos para todo el modelo y que si la tabla tiene nombre compuesto, no se debe dejar espacio entre las palabras para no tener que rodearlas entre comillas simples.

• Una de las tareas más importantes y recurrentes en informes analíticos es la creación de agregados.

• DAX tiene las funciones necesarias para crear esta funcionalidad. Tenemos SUM, MIN, MAX y AVERAGE.

• Una de las tareas más importantes y recurrentes en informes analíticos es la creación de agregados.

• DAX tiene las funciones necesarias para crear esta funcionalidad. Tenemos SUMX, MINX, MAXX y AVERAGEX.

• Las condiciones lógicas en DAX son expresiones o comparaciones que se evalúan para devolver un valor.

• Es muy similar a la expresión lógica SI de Excel.

• Evalúa una expresión en una lista de valores y devuelve una de varias expresiones de resultado posibles.

• Esta función se puede usar para evitar tener varias instrucciones IF anidadas.

• Las medidas no pueden hacer referencia a variables definidas fuera de la expresión de medida, pero pueden hacer referencia a variables de ámbito funcional definidas dentro de la expresión.

• Las variables pueden hacer referencia a medidas.

• Regresa una tabla con el subconjunto de otra tabla o expresión.

• FILTER(<tabla>,<filtro>)

• Por lo general se utiliza con COUNTROWS(<tabla>)

Las funciones find() y search()

El operador IN en DAX es una herramienta que permite evaluar condiciones de tipo OR lógico. Su función es devolver un valor de TRUE si una fila de valores existe o está contenida en una tabla y FALSE en caso contrario.

Una tabla calendario o de fechas almacena una fila por fecha y admite el requisito común de filtrar o agrupar por períodos específicos de fechas, como años, trimestres o meses.

Para ordenar datos por fecha en Power Pivot, puedes seguir estos pasos:

• Seleccionar la columna que se va a ordenar

• En la pestaña Inicio, en el grupo Ordenar, elegir el icono para ordenar la columna en orden ascendente o descendente

Las jerarquías facilitan a los usuarios seleccionar y navegar por rutas comunes de datos al crear informes y tablas dinámicas.

• ¿Algo para mejorar en esta sección?

• ¿Algún comentario o aporte?

• Tus comentario son muy importantes y seguiré trabajando para que puedas calificar este curso con 5 estrellas.

• Devuelve todas las filas de una tabla, o todos los valores de una columna, omitiendo los filtros que se puedan haber aplicado.

• Esta función es útil para borrar filtros y crear cálculos en todas las filas de una tabla.

• Los resultados de las expresiones DAX van a depender del contexto de filtro que se aplique sobre las tablas.

• Regresa una tabla con los valores distintos de una columna. Quita los valores duplicados.

• Se usa con otros parámetros, como suma, promedio o contar.

• DISTINCT(<column>)

• Los resultados de DISTINCT se ven afectados por el contexto de filtro actual.

• Regresa una tabla con los valores distintos de una columna. Quita los valores duplicados.

• Se usa con otros parámetros, como suma, promedio o contar.

• VALUES(<columna>)

• Cuando se usa la función VALUES en un contexto que se ha filtrado, los valores únicos que devuelve VALUES se ven afectados por el filtro.

Desde la tabla principal de una relación, devuelve todas las filas excepto la fila en blanco, o todos los valores distintos de una columna excepto la fila en blanco, y no tiene en cuenta ningún filtro de contexto que pueda existir.

• RELATED: devuelve un valor relacionado de otra tabla.

• RELATEDTABLE: evalúa una expresión de tabla en un contexto modificado por los filtros especificados.

• =COUNTX(RELATEDTABLE(Trimestre),Trimestre[Precio])

• =RELATED(Monedas[Pais])

• Recorre toda la tabla, fila por fila.

• La función FILTER usa contexto de fila.

• Los iteradores utilizan contexto de fila (sumx, minx, maxx, etc).

• Se utilizan en columnas calculadas, principalmente.

• Podemos utilizar dos iteradores para hacer cálculos.

• Es muy intuitivo, pero no es nada eficiente.

• Devuelve las N filas superiores de la tabla especificada.

• TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>, [<Order>]]…])

- 0 (cero) o FALSE. Ordena de forma descendente los valores de Order. Es el valor predeterminado si se omite el parámetro Order.

• La función CALCULATE() evalúa una expresión en un contexto de filtro modificado.

• CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

• Ventas Instructor MNX:=CALCULATE(

• SUM(Trimestre[Instructor]),

• 'Trimestre'[Moneda] = "mxn"

• )

• La transición de contexto pasa del contexto de fila a contexto de filtro.

• Las funciones que pueden realizar la transición de contexto es CALCULATE.

• Las medidas cuentan con una función CALCULATE implícita.

Cuando estamos haciendo un análisis con la tabla dinámica, nos puede interesar hacer una comparación entre los valores.

• Cuando creamos medidas con el modelo tabular (tablas), este cálculo va a depender de los distintos filtros que se realizan por las tablas.

• Hay ocasiones en las que necesitamos que nos devuelva el valor total sin que se afecte por ninguno de los filtros que se apliquen.

Algunas veces necesitamos realizar un filtro entre diferentes columnas, lo cual no se puede hacer con Calculate.

• La dirección de filtro entre tablas del modelo tabular en Power Pivot siempre ocurre de uno a muchos y no lo podemos cambiar en el modelo.

• Esto está bien, siempre y cuando el cálculo que nosotros hagamos esté basado en la tabla que está en el lado muchos de la relación.

• ¿Algo para mejorar en esta sección?

• ¿Algún comentario o aporte?

• Tus comentario son muy importantes y seguiré trabajando para que puedas calificar este curso con 5 estrellas.

Una herramienta fundamenta y muy poderosa para el análisis de datos es Excel. Dentro de la serie de heramientas desarrolladas por Microsoft para este fin contamos con Power Query que nos permite limpiar, extraer y tranformar (ETL Extract, Transform and Load) desde la mayoría de los principales formatos de intercambio de datos como XML, CSV, Access, JSON y por supuesto de Excel.

En esta clase puedes descargar los archivos del curso.