¡Descarga Power Query Tutorial Paso a paso y más Ejercicios en PDF de Informática solo en Docsity!
UNIVERSIDAD DEL PACÍFICO
Informática para los Negocios I Ciclo 2020 - 1
Ejercicio 1 Power Query
En este ejercicio vamos a demostrar el uso de diversos comandos disponibles en PowerQuery.
DATOS DE ENTRADA
Empecemos describiendo los datos que se tienen disponibles al inicio. Archivo: UPInfoNegoIU2A002 - Ejercicio 1 - Archivo 1 Hoja: REPORTE Tabla: Datos sin estructura de tabla. Encabezados no corresponden a la primera fila de la hoja. Contenido: Datos transaccionales de ventas en los diferentes locales, especificando: Factura, Fecha, Cliente, Sede, Vendedor, Garantía Extendida y Montos por cada marca. Los datos tienen columnas combinadas, celdas en blanco y distribución de datos no plana. Archivo: UPInfoNegoIU2A002 - Ejercicio 1 - Archivo 2 Hoja: VENDEDORES Tabla: Datos con estructura de tabla. Contenido: Datos de los vendedores: Local, ID, Nombre, Comisión
DATOS DE SALIDA
Se busca generar 2 reportes: Ventas por cada Sede, Marca y Mes. Datos sobre vendedores: Sede, ID vendedor, Montos totales, Cantidad de Operaciones, Monto mayor, Comisiones.
Paso 2 : Cambiando los tipos de datos. Power Query hace un reconocimiento automático de los tipos de datos. Puede verificar los tipos de datos haciendo clic sobre cualquier campo o celda de la tabla y visualizando la cinta Inicio/Tipo datos. También puede verificarlo viendo directamente el nombre del campo donde hay símbolos descriptivos ABC, 123 , 1.2, etc. Paso 3 : Terminando la consulta. Ya que la tabla origen se llama Vendedores, la consulta ha sugerido el mismo nombre. Lo dejaremos así. Ahora debe cerrar la consulta, pero no exportar los resultados nuevamente a Excel (no tendría sentido exportar a una hoja de Excel una tabla idéntica a la que ya existe) Clic en la cinta Inicio/Cerrar y cargar en… Elegir “ Crear solo conexión ” y Aceptar. (en el caso eligiera “Cerrar y cargar” y se desplegara una tabla con la información podría eliminar la tabla para que solo permanezca la consulta como resultado.) GRABE SU ARCHIVO, no lo cierre todavía.
Segunda etapa Crear una consulta que transforme los datos de la hoja REPORTE en una tabla, arreglando los problemas que presenta la información. Aunque la consulta puede llamarse de cualquier manera la llamaremos DATA. Paso 4 : Obteniendo los datos de Archivo 1. Vamos a obtener acceso al Archivo 1, desde nuestro Archivo 2. Clic en la cinta Datos/Obtener datos/Desde un archivo/Desde un libro Busque el Archivo 1 y haga clic en el botón Importar.
Paso 6 : Promoviendo encabezados. Promover la primera fila para que está represente los encabezados. Clic en la cinta Inicio/Usar la primera fila como encabezado Paso 7 : Cambiando los tipos de datos. Dependiendo de la versión de Excel, este paso se puede generar de manera automática. Si no se genera entonces debemos cambiarlo manualmente. Verifique que la columna “Fecha” es del tipo “Fecha” y que se aprecian fechas y “null” como valores. Si el tipo de datos no ha cambiado debe cambiarlo a Fecha en la cinta Inicio/Tipos de datos/Fecha
Paso 8 : Rellenando hacia abajo. Buscamos rellenar los espacios del campo Fecha. Para ejecutar este paso los valores vacíos deben figurar como “null”. Seleccione la columna Fecha y clic en la cinta Transformar/Rellenar/Abajo Paso 9 : Dividiendo por delimitador. Dividiremos el campo Sede/Vendedor en sus dos componentes Seleccione el campo Sede/Vendedor y clic a la cinta Transformar/Dividir columna/Por delimitador En la ventana “Seleccione o escriba el delimitador” seleccione --Personalizado-- y en el cuadro escriba la raya vertical | y clic en Aceptar.
Paso 12 : Planeando la tabla. Ahora vamos “planear” nuestra tabla pues tenemos cada monto referido a una marca en una columna diferente. Usando la tecla Shift seleccionar las columnas desde “Factura” hasta “Garantía Extendida”. Seleccione la cinta Transformar/Anular dinamización de columnas/Anular dinamización de otras columnas La tabla queda así: Cambiar “Atributo” por “Marca” y “Valor” por “Monto”.
Paso 13 : Redondeando números. Redondear los montos. Los montos contienen muchos decimales y es necesario redondearlos. Seleccionar el campo “Monto” y clic en la cinta Transformar/Redondeo/Redondear… Escribimos 2 en “Posiciones decimales” y clic en Aceptar.
Paso 16 : Dividiendo columna por delimitador. Ahora separamos el apellido materno con el mismo procedimiento del paso anterior. La tabla queda así: Paso 17 : Combinando columnas. Ahora unimos los apellidos. Seleccionar ambas columnas (Cliente.1 y Cliente.2.1). Clic en la cinta Transformar/Combinar columnas Luego en el cuadro “Separador” selecciona “Espacio” y en el cuadro “Nuevo nombre de columna (opcional)”la puede dejar en “Combinada”. Clic en Aceptar. Paso 18 : Cambiando texto a mayúsculas. Convertimos los apellidos a mayúsculas. Seleccionar la columna “Combinada” y clic en la cinta Transformar/Formato/MAYÚSCULAS
Paso 19 : Cambiando el texto a nombre propio. Mejoramos formato de los nombres. Seleccionar la columna “Cliente 2.2” y clic a la cinta Transformar/Formato/Poner En Mayúsculas Cada Palabra La tabla queda así: Paso 20 : Combinando columnas. Volvemos a juntar apellidos y nombres Seleccionar las columnas “Combinada” y “Cliente 2.2”. Seleccione la cinta Transformar/Combinar columnas En el cuadro “Separador” seleccione --Personalizado-- En el cuadro de abajo escribir una coma y un espacio. En el cuadro “Nuevo nombre de columna (opcional)” escriba Cliente. Clic en Aceptar.
Tercera etapa A partir de la consulta DATA generar un reporte con las ventas según Marca, Mes y Sede. Paso 22 : Crear una consulta nueva a partir de una consulta existente. Nuestro punto de partida será la consulta apenas generada DATA. En panel “Consultas y conexiones”, sobre DATA, botón de derecho del mouse y elegir Referencia. Paso 23 : Insertando el mes. Agregamos el número de mes. Seleccione la columna Fecha y clic en la cinta Agregar columna/Fecha/Mes/Mes
Paso 24 : Agrupando filas. Generamos una agrupación de filas en base a Mes, Sede y Marca. Los valores que se acumularán serán el Monto y se calcularán tanto la suma total como el monto máximo. Clic a la cinta Transformar/Agrupar por En la ventana que aparece active la opción “Uso avanzado” para que pueda agregar más columnas. Agrupar por: Mes, Sede, Marca (clic al botón “Agregar agrupación” para agregar más columnas) Nuevo nombre de columna: “Total”, Operación: “Suma”, Columna: “Monto” Nuevo nombre de columna: “MontoMax”, Operación: “Máx”, Columna: “Monto” Clic en Aceptar.
Paso 27 : Generando tablas de doble entrada. Ahora generamos la tabla de doble entrada que constituirá nuestro reporte. Seleccionar la columna Mes, clic a la cinta Transformar/Columna dinámica En “Columna de valores” seleccionar Total En “Opciones avanzadas” asegúrese que la función de valor agregado sea la Suma Obteniéndose: Por ejemplo, el resultado en la primera fila de Ate, LG y mes 1 viene de: La suma del monto es 1196.5.
Paso 28 : Terminando la consulta. Modificar el nombre de la consulta en el panel Configuración de la consulta. Nombre: VentasxMarcasxMesxSede En la cinta Inicio/Cerrar y cargar en… Elegir “Tabla” y Aceptar. Resulta: GRABE SU ARCHIVO, no lo cierre todavía.
