Tratamiento avanzado de datos con Excel y PowerBI

En esta práctica exploraremos las diferentes posibilidades de transformación que nos ofrece Power Query (Obtener y transformar) a partir de los datos incorporados al editor de consultas.

Para la realización de esta práctica deberás descargar y descomprimir el siguiente archivo:

• TransformarDatos
  

La práctica la realizaremos sobre el libro transformar-datos.xlsx.

Tareas a realizar

Explorar editor de consultas

• Como iniciarlo
  
• Situados en una tabla procedente de consulta: Herramientas de Consulta > Editar > Editar
• Datos > Obtener y transformar > Mostrar consultas y doble clic sobre la consulta o menú contextual
• Datos > Obtener y transformar > Nueva consulta > combinar consultas > Iniciar el Editor de consultas
• Ventana del Editor de consultas
  
• Expandir y contraer panel de consultas
• Pestañas: Archivo, Inicio, Transformar, Agregar columna y Vista
• Panel de navegación de consultas
• Área de tabla
• Panel de configuración de la consulta
• Barra de fórmulas

Los pasos de la consulta

• Explorar consulta ContactosBarcelona
  
• Ver en Editor avanzado
• Cambiar nombres pasos
• Tipo cambiado -> TipoCambiado

Cambiar origen

• Imprescindible si cambian la ruta de los archivos

Eliminar filas y columnas

• En ContactosValencia
  
• Eliminar TipoCambiado
• Reducir filas > Quitar filas > Quitar filas superiores: 1
• Transformar > Usar primera fila como encabezado
• Automáticamente se crea el paso Tipo Cambiado: revisar
• En ResumenContactos
  
• Eliminar TipoCambiado
• Reducir filas > Quitar filas > Quitar filas superiores: 1
• Transformar > Usar primera fila como encabezado
• Automáticamente se crea el paso Tipo Cambiado: revisar
• Cambiar título columna Etiquetas de fila a Año
• Reducir filas > Quitar filas > Quitar filas inferiores: 1
• Eliminar columna Total general
• En IBEX
  
• Quitar cuarta columna (sin título)
• En Carpeta texto sin encabezado
  
• Renombrar columnas a: OrigenDatos, Mes, Producto, Cantidad

Reemplazar valores

• En ContactosBarcelona
  
• Hijos: null -> 0
• Ahorros: null -> 0
• En ContactosValencia
  
• Hijos: null -> 0
• Ahorros: null -> 0

Filtro de datos

• En ContactosBarcelona 
  
• Hijos: desactivar 0
• En ContactosValencia
  
• Ingresos mensuales >= 1000
• En Movimientos
  
• Fecha entre 1/7/16 y 30/09/16
• En IBEX 
  
• Volumen > 1000000

Establecer tipo de datos

• Importantísimo con columnas de fecha y de números
• En ContactosBarcelona
  
• (seleccionar paso Tipo cambiado)
• Ingresos mensuales -> Moneda
• Ahorros -> Moneda
• Peso Kg -> Decimal
• En ContactosValencia
  
• (seleccionar paso Tipo cambiado)
• Ingresos mensuales -> Moneda
• Ahorros -> Moneda
• Peso Kg -> Decimal
• En ResumenContactos
  
• (seleccionar paso Tipo cambiado)
• Año -> Número entero
• En Facturas
  
• FechaPedido, FechaEntrega y FechaEnvío -> Fecha
• (con Ctrl seleccionar las tres columnas a la vez)

Unir y dividir columnas

• En ContactosBarcelona
  
• Poner Nombre a la izquierda de Apellidos (se crea el paso Columnas reordenadas)
• Nombre y Apellidos: Combinar columnas (ficha Agregar columna); separador espacio; nombre: NombreCompleto (¡Observar que el orden de selección es importante!)
• Fecha nacimiento: Dividir columna por delimitador
• Renombrar columnas Día, Mes y Año
• Iniciales: nuevas columnas inicial nombre, inicial apellido, combinarlas y eliminarlas.
• Renombrar paso como Iniciales
• En ContactosValencia
  
• Nombre y Apellidos: Combinar columnas (ficha Agregar columna); separador espacio; nombre: NombreCompleto  (¡Observar que el orden de selección es importante!)
  
• Fecha nacimiento: Dividir columna por delimitador
• Renombrar columnas Día, Mes y Año

Crear columnas personalizadas

• En Facturas
  
• ImporteTotal = PrecioConDescuento + Cargo
• Renombrar paso ImporteTotal
• Cambiar nombre columna en la misma fórmula
• En movimientos
  
• Entradas = Si (E/S = "E"), Unidades, sino 0  (columna condicional )
• Renombrar paso Entradas
• Salidas = Si (E/S = "S"), Unidades, sino 0.
• Renombrar paso Salidas
• Nombre mes = nombre mes (Fecha)
• En IBEX
  
• Signo = signo(Dif)
• Evolución = si Signo = 1, "Crece"; si Signo = 0, Igual; si Signo=-1, "Disminuye"; sino "Esto es imposible".
• Renombrar paso Evolución

Dinamizar y eliminar dinamización de columnas

• En Carpeta Texto sin encabezado
  
• Dinamizar Producto sumando Cantidad
• en ResumenContactos
  
• Anular la dinamización de  todas las columnas diferentes de Año
• Renombrar columna Atributo a Población y Valor a Usuarios

Rellenar celdas vacías (y más cosas)

• Crear una consulta desde un nuevo origen de datos: biblioteca.xlsx, hoja CONSULTAS
  
• Esta hoja tiene diferentes consultas, habrá que eliminar columnas para dejar la que nos interesa, libros por género y país.
  
• Eliminar el paso Tipo cambiado
• Seleccionar las columnas 4, 5 y 6 y eliminar las restantes
• Eliminar las primeras 3 filas.
• Promover a encabezado la primera fila
• En la columna GENERO, rellenar celdas vacías
• Filtrar eliminando todas las filas de GENERO que comiencen por "Total"
• Dinamizar GENERO sumando Total
• Reemplazar valores null por cero
• Crear una nueva columna que sume todos los géneros por país. 
• Modificar la fórmula para que la columna se llame Total
• Ordenar por columna Total, de mayor a menor.

Combinar y anexar consultas y expandir datos de tabla

• En movimientos
  
• Combinar con Productos por el IdProducto
• Expandir la nueva columna "Productos", para que muestre NombreProducto, sin usar como prefijo el nombre de la consulta original.
• En ContactosBarcelona
  
• Anexar con ContactosValencia en una nueva consulta llamada BarcelonaValencia.
• Observar que ocurre en la columna Iniciales (no existía en Valencia)
• En Clientes
  
• expandir la columna Pedidos, para que muestre FechaPedido y Empleados
• expandir la columna Empleados para que muestre Nombre y Apellidos, usando el nombre de la columna original como prefijo
• Crear nueva columna personalizada Empleado que concatene Empleados.Nombre y Empleados.Apellido.  Hacerlo con fórmula personalizada.

Referencias a consultas como origen de otras consultas

• Crear una consulta desde biblioteca.xlsx, hoja Libros
• Crear una referencia a la consulta libros (recién creada)
  
• Filtrar libros de género POESIA
• Renombrar la consulta a LibrosPoesía
• Crear otra referencia a la consulta libros (recién creada)
  
• Filtrar libros de género NOVELA
• Renombrar la consulta a LibrosNovela

Agrupar por uno o más campos

• Crear una referencia a la consulta Facturas
  
• Renombrarla como FacturasVendedorAño
• Transformar la columna FechaPedido para que sea solo el número de año.
• Eliminar las columnas excepto Vendedor, FechaPedido y ImporteTotal
• Sumar ImporteTotal agrupado por Vendedor y FechaPedido, nueva columna: ImporteTotal, también Recuento de filas en nueva columna NúmeroPedidos
• Calcular nueva columna PedidoMedio = ImporteTotal / NúmeroPedidos
• Cambiar paso a CálculoPedidoMedio

Exploración del editor avanzado de consultas y de las fórmulas (lenguaje M)

• Modificar ContactosBarcelona
  
• Reemplazar los cuatro últimos pasos por uno solo para calcular las iniciales
• Observar que en M,  la función equivalente a IZQUIERDA es Text.Start([Nombre], 1)
• La fórmula resultante es: Text.Start([Nombre],1)&Text.Start([Apellidos],1)

Modificar propiedades de la conexión

• Configurar la conexión de la consulta IBEX para que se actualice cada 15 minutos