Análisis de datos e inteligencia de negocio (Ad y In) con Excel y Power BI. Data Analysis and Business Intelligence (DA and BI)

Casos Propuestos (Mis Casos)

1 Casos ETL y Modelado de Datos

2 Casos TD y DAX Básico

3 Casos Power BI y DAX Básico

4 Casos propuestos para su desarrollo

5 SERIE: Análisis de Estados Financieros.Explotando el diario de contabilidad

1 Casos ETL y Modelado de Datos

Los conceptos a aplicar a los casos propuestos son los siguientes:

Estudio del caso

Diseño del esquema de modelo de datos

Conocer el objetivo y reflexionar sobre las preguntas clave a las que debe dar respuesta los informes a diseñar, es decir que debe contener el informe a diseñar, indicadores KPis, etc

Este análisis previo determina los datos que necesitamos y confirmar de que información disponemos para incorporar al modelo.

Identificación de fuentes necesarias y/o disponibles para incorporar al modelo de datos e identificar tabla de hechos y tabla/s de dimensión.

Modelo Estrella.

Relaciones y tipos

Es altamente recomendable comenzar con una hoja en blanco e intentar pensar en cómo será el modelo de datos según el proceso de negocio que se quiere analizar e identificar todos los filtros o dimensiones asociados.
 
   
Caso Conceptos y técnicas requeridas Ficheros relacionados
100a Caso Nuestro 1º informe Casos básicos con TD y GD con Excel. Comenzando a aprender Data Science y Business Intelligence con Excel.
Resumen: Comenzando a aprender Data Science y Business Intelligence con Excel.
Convirtiendo rango de datos en tablas. Asignando o cambiando nombre a las tablas.  Formato a las tablas. Formato condicional.
Creando tablas y graficos dinámicas basicas. trabajando con columnas dinámicas.Creación y configuración de segmentadores. Conexiones de los segmentadores con las tablas y graficos dinámicos
Original
Propuesta
101 Empleados

Creacion de una consulta, conexión a un fichero fuente no normalizado.
Uso de las opciones “Quitar Filas Superiores“ y “Encabezados Promovidos”  opcion Usar la Primera fila como encabezado
Aplicamos filtros para no disponer de determinadas filas, por ejemplo que contiene null en una determinada columna. 
Uso de la opcion “Rellenar Abajo” que accedemos usando el botón derecho sobre la columna deseada y nos da acceso entre otras opciones a esa.
Limpieza. Eliminar los registros que tienen espacios al comienzo y o al final, usando la opcion Transformar-Recortar.
Separar nombre y apellido, con la opcion “Dividir Columnas por delimitador
Uso de “Remplazar Valores”, por ejemplo, cambiar null por 0
Cambiar tipo de datos de las columnas a traves del icono izquierdo

Original
Propuesta

 

102 Ventas 7009 dos tablas Creacion de una consulta, conexión a un fichero fuente no normalizado. Relacionar dos consultas. Combinar-Combinar Consultas Uso de las opciones “Quitar Filas Superiores “y “Encabezados Promovidos” opcion Usar la Primera fila como encabezado. Quitar filas en blanco Quitar columnas. Cambiar tipo. Dividimos columnas por número de caracteres y creación de una columna personalizada por ejemplo: [Compañía Cliente.1]&" "&[Compañía Cliente.2] Limpieza. Eliminar los registros que tienen espacios al comienzo y o al final, usando la opcion Transformar-Recortar. Uso de “Remplazar Valores”, por ejemplo, cambiar null por 0. Quitar espacios en blanco y caracteres no deseados como guiones, puntos, etc. Transformación sobre campo fecha, en año, trimestre, semanas, etc. Original
Propuesta
103 Quantity Muy sencillo. Creacion de una consulta, conexión a un fichero fuente no normalizado tipo CSV.
Uso de las opciones “Encabezados Promovidos” opcion Usar la Primera fila como encabezado
Aplicamos filtros para no disponer de determinadas filas, por ejemplo, que contiene NA en una determinada columna. Quitar vacíos Cambiar tipo de datos de las columnas a traves del icono izquierdo, pasamos de texto a numero entero
Original
Propuesta
104 SABI - Revisado Fundamental. Encabezados promovidos, quitar columnas, columna de anulación de dinamización, dividir columnas por transición de caracteres o separando por últimos caracteres, columnas por nombre cambiado, tipo cambiado de columnas (texto por numero), quitar errores, etc. Original
Propuesta
105 ETL Produccion Base Miguel Caballero Anulando dinamización, conversión a modelo tabular.
Resumen: integración en una sola consulta el fichero de datos aplicando anulación de dinamización.
Técnicas y palabras clave: Anular dinamización
Original
Propuesta
106 Diario y Plan txt

Realizar una consulta dos orígenes de datos de texto plano. Cambiar manualmente el nombre a las columnas. Agregar Columna – Columna Condicional agregando condiciones y fórmulas. Combinar dos Consultas. Creacion de una consulta referenciada. Otras acciones como Transformar-Agrupar por.

Original
Propuesta
107 EBI Diario PQ y PPivot Power query preparación de un diario de contabilidad con Power Query y Power Pivot. Mi primer modelo de datos y tabla dinámica. Realizar una consulta dos orígenes de datos fichero Excel.
Depuración en columnas. Cambiar manualmente el nombre a columnas. “Agregar Columna – Extraer – Primeros Caracteres”. “Cambiamos el tipo”. Eliminamos aquellas columnas vacías. Remplazamos null por 0 con Inicio-Remplazar Valores,
Limpiamos filas. “Quitar Filas – Quitar Filas Superiores”, Transformar – Usar la primera fila como encabezado
Combinar dos Consultas. Creacion de una consulta referenciada
Otras acciones como: Combinación de consulta. con la opcion “Inicio - Combinar Consultas”. Creacion de nuestro primer modelo de datos con Power Pivot. Creación de nuestra primera tabla dinámica basada en modelo de datos.
Original
Propuesta
108 Ventas por empleados Años Anexar varias consultas en una sola. Combinar consulta con relación múltiple, varios campos. Conectar Carpetas
Realizar una consulta dos orígenes de datos fichero Excel. Anexar varias consultas en una nueva. Combinar dos Consultas con relación múltiple. Conectar carpetas
Original
Propuesta
109 ETL Pais Base Miguel Caballero Anulando dinamización de otras columnas a modelo tabular.
Resumen: integración en una sola consulta el fichero de datos aplicando anulación de dinamización.
Técnicas y palabras clave: Anulando dinamización de otras columnas
Original
Propuesta
111 ETL Integración de carpetas Base Miguel Caballero

Resumen: Integración de todos los ficheros actuales y futuros contenidos en una carpeta. integración en una sola consulta de la información de los ficheros contenidos en una misma carpeta
Técnicas y palabras clave: Crear conexiones a una carpeta, cambiar tipo a columna fecha de numero a fechas

Original
Propuesta
112 ETL Combinar Consultas Base Miguel Caballero

ETL y Modelo de Datos. Combinar consultas en PQ
Resumen: Combinar consultas en PQ. El sustituto de BuscarV  o relacionando tablas como en Power Pivot o Access..
Técnicas y palabras clave: Grupo Combinar, combinar consultas

Original
Propuesta
113 ETL Agrupar por dos criterios o mas, Base Miguel Caballero ETL y Modelo de Datos. Agrupar una consulta por dos o más criterios
Resumen: Usar PQ para agrupar datos por doble criterio, nº de factura y máxima fecha
Técnicas y palabras clave: Crear conexiones y utilizar las opciones de agrupar por avanzada
Original
Propuesta
120 Movimientos de almacen.

Resumen: Usar PQ con el editor avanzado para copiar conexiones y pasos aplicados a cada consulta con el Editor Avanzado de Consulta de PQ (Power Query – Vistas – Editor Avanzado). Función muy útil aplicable por ejemplo para el caso de los diarios contables de A3 cuando aparecen diario por mes en pestañas u hojas distintas.  
Técnicas y palabras clave: Copiar y pegar consulta asi como copiar y pegar pasos aplicados en las consultas a traves del Editor Avanzado de Power Query. Quitar filas superiores. Usar primera fila como encabezado.

Original
Propuesta
130 Diario A3 Tabluar

Creación de una plantilla para el diario de A3. Consolidar las hojas de un diario de A3. Creación de una plantilla para el diario de A3 “Diario A3 tabular”. Consolidar las hojas (meses) de un diario A3 para un periodo con el Editor Avanzado de Power Query.
Resumen: Contienen muchas de las opciones de uso más frecuente en el Proceso ETL con Power Query. Caso diario de contabilidad de A3.
Técnicas y palabras clave: Creacion de conexión. Quitar filas superiores. Usar la primera fila como encabezado. Convertir los tipos de datos. Filtrar valores con null o en blanco. Rellenar celdas en blanco. Dividir columna – crear una columna condicional - crear columna personalizada. Trabajando con el editor avanzado de PQ, copiando y pegando codigo. Analisis del codigo M generado en PQ. Anexar consultas

Original
Propuesta
     
     
     
     
     

Ver Modo de tabla a estandar

2 Casos TD y DAX Básico

Los conceptos a aplicar a los casos propuestos son los siguientes

Conceptos básicos

Aplicando Formato a las TD y GD

Ordenar, filtrar y agrupar TD y GD

Enriquecer datos con campos y elementos calculados en TD y GD

Creación de TD basada en un rango, tabla y modelo de datos.
Explorar las pestañas de "Análisis" y "Diseño"
Actualizar tablas dinámicas

Cambiar el formato de números
Formato de celdas en blanco
Aplicar formatos condicionales

Opciones de ordenar
Filtros de etiqueta y selecciones manuales. Filtros de etiqueta con comodines
Filtros de valor
Agrupar datos. Agrupar fechas automáticamente
Usar segmentación de datos y escalas de tiempo

Modos de resumir valores
Cálculos de "Mostrar valores como".: % del total de columnas/filas,  % del total principal, Diferencia de…
Definir campos calculados

 

   
Caso Conceptos y técnicas requeridas Ficheros relacionados
201 Línea Blanca Marrón y PAE Contamos con los registros de ventas del ultimo periodo de Línea Blanca (neveras, lavadoras, secadoras, lavavajillas, etyc..) Línea Marrón (Aparatos electrónicos de consumo TV, HIFI, Sonido…) y PAE (pequeños electrodomésticos, planchas, Freidoras, Aspiradoras, cafeteras, exprimidores y hervidores, etc..) en un fichero Excel en bruto sin formato. En base a la información disponible, las preguntas basicas a responder están relacionadas con:
Datos de ventas por región, producto y vendedor. Filtro y selección por estos parámetros.
Informe de venta con gráficos relacionados.

Desarrollar informes similares a los propuestos en las siguientes ilustraciones aplicando los conceptos y técnicas estudiadas.

Original
Propuesta
202 Finanzas de SKU-DON

Contamos con un informe en bruto de los registros de nuestra aplicación CRM sobre la facturación a lo largo del último periodo, total de registros 7.080. En la otra pestaña contamos con las bases de clientes (93) y productos (344). En base a la información disponible, las preguntas basicas a responder están relacionadas con:

  • Análisis de los Ingresos, filtrando por país, por producto, el top 5 de clientes, ingresos por periodo, por producto y el top 5 de productos.
  • Resultados y márgenes por región y clientes. Análisis del beneficio y márgenes por región y tipo de clientes.
  • Resultados y márgenes por tipo de prodcuto. Análisis del beneficio y márgenes por tipo de producto.
Desarrollar informes similares a los propuestos en las siguientes ilustraciones aplicando los conceptos y técnicas estudiadas.
Original
Propuesta
203 Llamadas Call Center Contamos con los registros de las llamadas recibidas de atención al cliente de nuestra empresa y nuestro aplicativo de control y registro presenta un fichero en csv. En base a la información disponible, las preguntas basicas a responder están relacionadas con:
  • Análisis de las llamadas en el periodo
  • Análisis de las llamadas por intervalo horario
  • Análisis de las llamadas por día de la semana
  • Y algunas otras cuestiones que puedan resultar de su interes

Desarrollar informes similares a los propuestos en las siguientes ilustraciones aplicando los conceptos y técnicas estudiadas.

Original
Propuesta
204 Estudio de Mercado de VideoJuegos Queremos llevar a cabo un análisis del mercado de videojuegos a nivel mundial y para ello nos han facilitado la siguiente información normalizada en un fichero csv. Debemos realizar entre otras operaciones las siguientes: campo aplicar “Anular dinamización de columnas”. Quitar la palabra ventas y con la opcion extraer texto despues del delimitador espacio Original
Propuesta
205 Modelo Básico de Ventas con TD CASO GUÍA
Análisis de un proceso de Business Inteligence centrado en el diseño de un modelo de ventas. Creacion de modelo de datos y conexiones, medidas básicas con inteligencia de tiempo y Tablas dinámicas. Modelo de Datos, DAX Básico y TD.
Indicadores y ratios: % Margen Comercial sobre ventas. Número de Clientes distintos. Numero de productos distintos vendidos. Numero de albaranes u operaciones de venta. Venta media. Venta media por cliente.
Técnicas y palabras clave: SUM, ABS, DISTINCTCOUNT, DIVIDE, CALCULATE, SAMEPERIODLASTYEAR. Crear tabla calendario desde Power Pivot para Excel. Creacion de columna format fecha. Crear Jerarquías en Power Pivot para Excel
Datos
Propuesta
206 Telemarketing y Kpi Introducción a las medidas y a los KPI. Conceptos teóricos relacionados con los KPI Datos
Propuesta
207 Control de calidad y KPI Introducción a las medidas y a los KPI. Conceptos teóricos relacionados con los KPI Datos
Propuesta
208 Producción Mecanica Rápida Cars Cuadro de mando con Tablas y Graficos Dinámicos basado en un modelo de datos .
Uso de PQ y P Pivot y medidas, funcione básicas Técnicas y palabras clave: Quitar filas superiores y promover encabezados. Anular dinamización. Cambiar nombre de columnas. Agregar columnas personalizas con mes, año y trimestre. Tipo cambiado para mostrar el margen como porcentaje. Creacion de medidas, funciones o campos calculados básicos.
Datos
Propuesta
299 de Excel a Power BI Importar un modelo de datos desarrollado con Power Pivot en Excel a Power BI  

3 Casos Power BI y DAX Básico

   
Caso Conceptos y técnicas requeridas Ficheros relacionados
300a 1º informe básico en Power BI. 1º Informe básico en Power BI sin modelo de datos ni medidas.Comenzando con Data Science y Business Intelligence con Power BI básico sin modelo de datos ni medidas.Creacion de nuestro primer Power BI, conectando con la fuente de datos. Obteniendo y visualizando datos.
Convirtiendo rango de datos en tablas. Asignando o cambiando nombre a las tablas.  Formato a las tablas. Formato condicional.
Primer contacto con Power BI, principales paneles, elementos visuales, filtros, configuraciones basicas, etc.
Visualizaciones básicas, visualizaciones de matriz y de graficos. Formato de visualizaciones.
Flitros y Slicers conexión y formato básico.Informe de ventas

Original
Propuesta
Visualizacion

300 Power BI Básicos I

Relación de casos básicos de Power BI de introducción al análisis y potencial de esta herramienta. Documentos correspondientes al curso de Ana María Bisbé York - Power BI esencial (Enlace)

Fuente
301 Liena Blanca, Marron y PAE Caso básico de Power BI basado en el 201 Línea Blanca Marrón y PAE Fuente
Solución
301b Vendedores y Presupuestos v1 Caso básico de Power BI primeras visualizaciones Fuente
Solución
302 Mercado del Video Juego Caso básico de Power BI basado en el 204 Estudio de Mercado de VideoJuegos Fuente
Solución
310 Recursos Humanos

Trabajando con campos fechas creacion de columna fecha de referencia, Edad de Referencia y Edad Actual. Uso de la opcion Columna Condicional procedemos a crear cuatro grupos de edad. Modelo de datos y relaciones simples. Tablas y graficos dinámicos basicos basados en modelo de datos.
Trabajando con DAX, creacion de columnas y medidas básicas: Uso de COUNT, AVERAGE, IF

Original
Propuesta
311 Deuda de Clientes Creación de columnas y medidas básicas: Uso de SUM, IF, Year y separando columnas Original
Propuesta
312 Análisis de ventas por países Medidas: SUM y SUMX
Visualizaciones: Gráfico De Anillos - Gráfico De Cascada- Gráfico De Cintas- Gráfico De Columna Agrupada - Gráfico De Columna Apilada- Gráfico De Líneas – Mapa - Matriz Con Jerarquía – Segmentadores – Tabla - Tarjeta
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Original
Propuesta
313 Aplicando otras medidas 1 Medidas: SUMX, COUNTROWS, DISTINCTCOUNT SUMX con RELATED, DIVIDE
Visualizaciones: Tabla - Matriz
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Propuesta
314 Aplicando otras medidas 2 CALCULATE Medidas: CALCULATE
Visualizaciones: Matriz, Tarjeta y Segmentadores
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Propuesta
315 Comprar datos con SAMEPERIODLASTYEAR Medidas: SAMEPERIODLASTYEAR
Visualizaciones: Matriz y Segmentadores
Casos basados en Power BI esencial de Ana María Bisbé York (Enlace)
Original
Propuesta
316 Ventas Canarias SUMX con RELATED

Query: Copiar consulta, separar columnas, quitar columnas, eliminar filas
Medidas: SUM, SUMX con RELATE, DIVIDE
Visualizaciones: Tarjeta, Mapa, Matriz y Segmentadores

Original
Propuesta
317 Ventas Canarias v.2 CALCULATE v otros Medidas: COUNTROWS, AVERAGE, DIVIDE y CALCULATE
Visualizaciones: Tarjeta y Segmentadores.
Original
Propuesta
318 H&8 Distribuciones Medidas: SUM y DIVIDE
Visualizaciones: Tarjeta, segmentadores, cuadro de texto, gráfico de barras apilada, matriz y gráfico de columnas apilada
Datos
Propuesta
Visualizacion
     
320 Telemarketing y KPI TD y DAX. Introducción a las medidas y a los KPI. Conceptos teóricos relacionados con los KPI Original
Propuesta
321 Control de Calidad y KPI TD y DAX. Introducción a las medidas y a los KPI. Conceptos teóricos relacionados con los KPI Original
Propuesta
     
330 330 Modelo Básico de Ventas con PBI

Análisis de un proceso de Business Inteligence centrado en el diseño de un modelo de ventas. Creacion de modelo de datos y conexiones, medidas básicas con inteligencia de tiempo y Tablas dinámicas. Conectar y navegar entre paginas y elementos visuales. Control de las iteaciones de los segmentadores.
Indicadores y ratios: % Margen Comercial sobre ventas. Número de Clientes distintos. Numero de productos distintos vendidos. Numero de albaranes u operaciones de venta. Venta media. Venta media por cliente.
Técnicas y palabras clave: SUM, ABS, DISTINCTCOUNT, DIVIDE, CALCULATE, SAMEPERIODLASTYEAR. Crear Jerarquías. Trabajando con segmentadores y control de sus iteracciones. Menú basado en botones y marcadores.

Datos
Propuesta
Visualizacion
331 Frecuencia de compras de clientes Estudiar la frecuencia de compra de los clientes:
Funciones DAX involucradas: Funciones de tabla y selección: SELECCTCOLUMS, Funciones de inteligencia de tiempo: FIRSTDATE - LASTDATE , Funciones de Fecha y hora:  DATEDIFF, Funciones Filtro: RELATEDTABLE, Funciones Estadísticas: COUNTROWS, Funciones Matemáticas: DIVIDE
Datos
Propuesta 1
Propuesta 2
     
     
     

 

4 Casos propuestos para su desarrollo

   
Caso Conceptos y técnicas requeridas Ficheros relacionados
900 TPV I

Información facilitada del registro de TPV correspondiente a una cafetería. Entre los aspectos técnicos a considerar se requiere aplicar los siguientes:

  • Crear rangos de edad de los camareros, para la fecha máxima de los datos de ticket.
  • Crear y trabajar con tabla calendario para dias de la semana, mes, etc..
  • Contemplar franjas horarias para el análisis (mañana y tarde o mañana, mediodía y tarde, etc.)
  • Crear KPI

Fuente

901 Atencion Cliente suministro eléctrico Contamos con el registro diario de la actividad relacionado con la comercialización y gestión de un servicio de atención al público perteneciente a una empresa colaboradora de una compañía nacional del sector eléctrico de nuestro país.. Crear y trabajar con tabla calendario. Geolocalización de datos Fuente
902 APP TAXI

Contamos con el registro diario de la actividad relacionado con la APP TAXI que ofrece los servicios de contratación de Taxi en distintos municipios de nuestra isla. Entre los aspectos técnicos a considerar se requiere aplicar los siguientes:

  • Crear y trabajar con tabla calendario para dias de la semana, mes, etc
  • Aplicación de KPI
Fuente
     
     

 

5 SERIE: Análisis de Estados Financieros. Explotando el diario de contabilidad

   
Caso Conceptos y técnicas requeridas Ficheros relacionados
EFE con TD Diseño de informes y cuadro de mando para el análisis del Cash Flow y Tesorería con tablas dinámicas
Creación de tabla calendario. Modelo de datos estrella. Medidas básicas en contabilidad: Saldo y Saldo Acumulado.Sobre las funciones de inteligencia de tiempo. SAMEPERIODLASTYEAR, LY, YoY y %YoY
Propuesta