Enunciado de la práctica 3

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:

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


Last modified: Friday, August 31, 2018, 7:11 PM