Cómo utilizar Power Query en Excel 2016 para analizar y manipular datos

La tarea de analizar datos a menudo implica pasar más tiempo obteniendo y limpiando los datos que analizándolos. Si eso te describe, definitivamente debes revisar Power Query de Excel 2016 (o Get & Transform). Usando Power Query, puedes cargar datos de diferentes fuentes, incluida la hoja de cálculo activa. Una vez que los datos están en Power Query, puedes analizar y manipular esos datos rápidamente utilizando herramientas especializadas. Si crees que la única forma de obtener los resultados que necesitas es mediante fórmulas complejas o incluso código VBA, haz una pausa y piensa, ¿puedo hacer esto en Power Query en su lugar? Muchas veces, la respuesta será sí.

¿Qué es un campo de valor múltiple?

Si estás familiarizado con la tecnología de bases de datos, sabrás que una de las reglas de normalización más importantes es que cada campo debe almacenar el valor más pequeño y autónomo posible. Muchas bases de datos y especialmente las hojas de cálculo rompen esta regla, mucho para la tristeza de sus usuarios. La Figura A muestra un conjunto de datos simple que rompe esta regla.

Figura A

Un campo de valor múltiple almacena uno o más valores.

Como puedes ver, tres registros (filas 3, 6 y 7) tienen más de un valor en la columna de Items. Tiene sentido, pero solo al principio. No pasará mucho tiempo antes de darse cuenta de que no puedes analizar ni manipular fácilmente los valores posteriores. Por ejemplo, ¿cómo contarías el número de elementos representados en este conjunto de datos? (Hay 16).

Si almacenaras estos datos en una base de datos relacional, tendrías al menos dos tablas: una para cada orden con una clave primaria que identifica de manera única cada registro (orden) y una segunda que almacena cada producto como un solo registro. Luego, usarías una consulta para juntar esos registros, uno para cada producto usando una clave primaria/externa para identificar qué productos van con qué orden. Usando esta estructura, puedes ordenar, agrupar e incluso analizar tus datos fácilmente por los valores de Items sin saltar por aros.

Cómo usar Microsoft To-Do: Guía completa para organizar tus tareas

Eso es lo que buscamos con la hoja simple mostrada en la Figura A. Queremos un registro para cada valor en la columna de Items. Específicamente, en lugar de 12 registros, queremos 16 y queremos una forma de agrupar los productos para cada orden. ¿Puedes ver el problema? Intentar hacer esto manualmente sería más tedioso y peor. Los desarrolladores serios recurrirían al código VBA, pero ¿qué pasa si no tienes esa habilidad? Gracias a Power Query, incluso los usuarios ocasionales pueden crear 16 registros a partir de los 12 en solo unos minutos:

• En primer lugar, cargaremos los datos en Power Query.
• Luego, agregaremos una columna de índice para que puedas ver visualmente qué productos pertenecen a la misma orden.
• A continuación, crearemos las nuevas filas utilizando una consulta de delimitador. Un delimitador es un carácter constante que separa los valores.

Es rápido y fácil.

Cargar datos en Power Query

Mencioné anteriormente que puedes cargar datos de muchas fuentes diferentes, te recomiendo que explores las muchas opciones (Figura B) por tu cuenta. Por ahora, vamos a cargar datos desde un objeto de tabla que ya se encuentra en Excel. Si tus datos son un rango normal, debes convertirlo en un objeto de tabla; de lo contrario, Excel te obligará a hacerlo cuando utilices la función.

Figura B

Power Query puede recuperar datos de muchas fuentes.

Para convertir un conjunto de datos en un objeto de tabla, haz clic en cualquier lugar dentro del conjunto de datos, haz clic en la pestaña Insertar, elige Tabla del grupo Tablas, especifica si el conjunto de datos tiene una fila de encabezado y haz clic en Aceptar.

Para cargar el objeto de tabla en Power Query, sigue estos pasos:

Hangouts Chat: La herramienta de chat empresarial de Google para colaborar en G Suite

1. Haz clic en cualquier lugar dentro de la tabla.
2. Haz clic en la pestaña Datos.
3. En el grupo Obtener y transformar datos, haz clic en Desde Tabla/Rango. (Si los datos no son una tabla, Excel te llevará a través de los pasos necesarios para convertir los datos en uno.)
4. Excel abrirá el Editor de Consultas (Figura C). Cada encabezado de columna identifica su tipo de datos para una confirmación visual fácil.

Figura C

El Editor de Consultas ofrece muchas herramientas para manipular y analizar datos.

Agregar una columna de índice

Este siguiente paso no es necesario, pero agrega un poderoso momento visual de "¡Ohhhhh!" que creo que apreciarás. Agregaremos un valor único a cada fila, pues recuerda, en este momento, cada fila representa una sola orden, pero algunas de las órdenes están compuestas por múltiples productos.

Para agregar una columna de índice:

1. Haz clic en el menú de consultas (el icono de hoja en la intersección de las filas de encabezado).
2. En este menú, elige Agregar columna de índice y luego selecciona Desde 1 en el submenu resultante (Figura D).

Figura D

Selecciona un valor inicial para la columna de índice.

Como puedes ver en la Figura E, cada registro tiene un valor único. La serie comienza con uno y es consecutiva. Tenemos 12 registros de orden.

Figura E

Cómo compartir y administrar Office 365 en tu negocio o hogar

Agrega una columna de índice al conjunto de datos.

Un poco de magia

Ahora estamos listos para hacer un poco de magia, o mejor dicho, dejar que Excel haga un poco de magia. Recuerda, queremos una sola fila para cada valor de Items, no cada registro. La solución es fácil:

1. Selecciona la fila de Items (haz clic en la celda de encabezado).
2. Haz clic en la opción Dividir columna en el grupo Transformar y elige Por delimitador en la lista desplegable resultante.
3. El cuadro de diálogo resultante te pide que identifiques el delimitador; en este caso, es el punto y coma (;) (Figura F), así que selecciona Punto y coma de la lista desplegable. Mantén la opción predeterminada, Cada aparición del delimitador, pero aún no hagas clic en Aceptar.
4. Haz clic en Opciones avanzadas.
5. En la sección Dividir en, selecciona Filas (Figura G) y haz clic en Aceptar.

Figura F

La consulta devuelve una sola fila para cada valor en la columna de Items.

Figura G

Especifica una división de filas.

Como puedes ver en la Figura H, cada elemento se ha movido a una fila propia, junto con la información de la orden: fecha, valor, etc. La columna de índice te ayuda a determinar qué productos pertenecen a la misma orden. Los valores de índice todavía identifican las 12 órdenes originales, pero hay más de una fila para la orden 1, 4 y 5, lo que indica que esas órdenes tienen varios elementos. Puedes guardar tu aro, no se requiere saltar a través de él. Aquí es donde dices, "¡Ohhhhhhh!"

Figura H

Power Query separa cada valor de elemento en una fila propia.

Cuando cierres Power Query, Excel te preguntará si deseas guardar o descartar la consulta. Si deseas trabajar con los datos, elige Guardar. Excel copiará los resultados de la consulta en una nueva hoja.

Cómo combinar campos de Word con una combinación de correspondencia

Envíame tu pregunta sobre Office

Respondo las preguntas de los lectores cuando puedo, pero no hay garantía. No envíes archivos a menos que se soliciten; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos serán eliminadas sin leer. Puedes enviar capturas de pantalla de tus datos para ayudar a aclarar tu pregunta. Al contactarme, sé lo más específico posible. Por ejemplo, "Por favor, revisa mi libro de trabajo y soluciona lo que está mal" probablemente no recibirá una respuesta, pero "¿Puedes decirme por qué esta fórmula no está devolviendo los resultados esperados?" podría sí. Por favor, menciona la aplicación y la versión que estás utilizando. No estoy recompensada por Newsmatic por mi tiempo ni mi experiencia cuando ayudo a los lectores, tampoco les cobro una tarifa a los lectores a los que ayudo. Puedes contactarme en [email protected].

Ver también

• Tres expresiones de suma acumulada en Excel (Newsmatic)
• Cinco consejos para utilizar eficientemente la lista de Autocompletar de Outlook 2016 (Newsmatic)
• Cómo usar VBA para seleccionar un rango en Excel (Newsmatic)
• Errores en Excel: cómo la hoja de cálculo de Microsoft puede ser peligrosa para tu salud (ZDNet)
• Office Preguntas y respuestas: cómo manejar el espaciado al final de una oración en Microsoft Word (Newsmatic)
• La IA ayuda a Google Sheets a comprender tus comandos en lenguaje natural (CNET)

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo utilizar Power Query en Excel 2016 para analizar y manipular datos , tenemos lo ultimo en tecnología 2023.