Cómo desvincular en Excel usando Power Query (3 formas)

La preparación de datos es probablemente el factor de éxito número uno en todos los análisis e informes de datos. Si el diseño de los datos es incorrecto, estará luchando constantemente con Excel; Tendrás que utilizar fórmulas muy avanzadas; Utilizará columnas innecesarias e incluso podría duplicar datos. Por lo tanto, aprender a desvincular en Excel usando Power Query es una de las habilidades más importantes que puede tener.
Solía sorprenderme de las personas que podían usar fórmulas complejas. Aprendí muchos consejos y trucos al aprender estas técnicas avanzadas. Pero pronto descubrí que obtener datos en el formato correcto desde el principio significaba que no necesitaba esas fórmulas avanzadas. habilidades. Porque una vez que los datos tienen la estructura adecuada, Excel se vuelve fácil.
Esta publicación le muestra cómo obtener los datos en la estructura correcta al desvincularlos en Excel usando Power Query.
Tabla de contenido
- ¿Qué significa desvincular sus datos?
- ¿Por qué desvincular en Excel?
- Desvinculación básica en Power Query
- Desvincular con múltiples columnas
- Desvincular con varias filas de encabezado
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0110 Desvincular datos.xlsx
Tener acceso
Ahora active Excel y abra el archivo de ejemplo. ¡Estamos listos para irnos!
Mira el vídeo en YouTube.
¿Qué significa desvincular sus datos?
A menudo obtenemos información categorizada en las columnas. Mira el ejemplo de abajo.
Podemos ver que enero , febrero y marzo están en columnas separadas. Pero esas columnas son categorizaciones del mismo tipo de datos. Por lo tanto, un mejor diseño sería tener una columna separada para cada mes.
El proceso de mover datos de columnas a filas se llama desvinculación . Dependiendo de con quién hable, puede denominarse aplanamiento o normalización de datos.
Antes de Power Query, transformar los datos requería un gran esfuerzo; Tuvimos que recurrir a fórmulas complejas o códigos VBA largos, pero Power Query tiene muchas características para facilitar esta transformación.
¿Por qué desvincular en Excel?
En la introducción, dijimos que los datos no pivotados hacen que sea más fácil trabajar con Excel. Quizás te preguntes ¿por qué? ¿Por qué molestarse en desvincular los datos en Excel?
Desvincular cambia su entrada de un diseño informativo/presentativo a un diseño de datos, que es eficiente para el uso de las computadoras.
Tener un formato optimizado para computadora hace que:
- Es más fácil analizar datos ya que podemos pivotar por cualquier categoría.
- Es más fácil crear visualizaciones ya que cualquier categoría puede estar en un eje.
- Más rápido para calcular grandes volúmenes de datos
En última instancia, todo se reduce a esto: los datos no dinámicos son el formato ideal para trabajar con tablas dinámicas , gráficos dinámicos, fórmulas y el modelo de datos. Entonces, apuntemos a que nuestros datos estén en ese formato.
Desvinculación básica en Power Query
A lo largo de tres ejemplos, avanzaremos desde diseños de datos fáciles a complicados, pero quedará claro que todo lo que se necesita es una base sólida en algunas transformaciones básicas utilizadas de la manera correcta.
Para nuestro primer intento de pivotar datos, utilizamos la hoja de trabajo del Ejemplo 1.
Los datos de origen muestran una columna separada para cada mes. Sin embargo, en lugar de tener una columna para cada mes, es mejor tener una columna que contenga el nombre del mes como atributo y una columna de valor único.
Seleccione cualquier celda en el rango de datos y cámbiela a una tabla de Excel haciendo clic en Insertar tabla (o presionando Ctrl + T ).
La ventana Crear tabla adivina el rango de datos y si hay una fila de encabezado. Dado que nuestros datos tienen una sola fila de encabezado, podemos seleccionar la opción Mi tabla tiene encabezados y luego hacer clic en Aceptar .
Dale a la tabla un nombre significativo (yo he elegido tblSalesData ).
Ahora estamos listos para cargar la tabla tblSalesData en Power Query. Seleccione una celda en la tabla y haga clic en Datos de tabla/rango .
Se abre el Editor de Power Query, que muestra una vista previa de los datos.
Elimine el paso Cambiar tipo . Este paso codifica los encabezados de las columnas en el código M, por lo que se romperá si obtenemos nuevas columnas en nuestro conjunto de datos.
Bien, ahora descubramos cómo desvincular los datos.
Hay tres opciones dentro del botón Transformar columnas sin pivote (desplegable ). La opción que elijamos depende del resultado que queramos.
De esas tres opciones, sólo hay dos resultados:
- Desvincular en la columna seleccionada
- Desvincular las columnas no seleccionadas.
¿Cuál es la diferencia? ¿Es importante?
Si seleccionamos las columnas enero , febrero y marzo y luego aplicamos Desvincular solo las columnas seleccionadas , se desvinculará de esas columnas. Power Query crea una nueva columna titulada Atributos que contiene meses.
El código M para lograr esta transformación sería:
= Table.Unpivot(#"Changed Type", {"January", "February", "March"}, "Attribute", "Value")
Los encabezados de columna de enero , febrero y marzo están codificados en el código M. ¿Qué sucede si agregamos datos adicionales para abril? Power Query no desvinculará la nueva columna de abril ya que no está incluida en el código. Pero si Es probable que agregue una nueva columna de atributo (por ejemplo, Tamaño), funcionará bien.
En su lugar, podríamos seleccionar las columnas Cliente y Producto y usar Desvincular otras columnas . Para nuestro conjunto de datos actual, logra el mismo resultado visual en la ventana de vista previa, pero el código M en la barra de fórmulas es diferente.
= Table.UnpivotOtherColumns(#"Changed Type", {"Customer", "Product"}, "Attribute", "Value")
El código no hace referencia a las columnas del mes. Por lo tanto, si agregamos meses adicionales a los datos de origen, esos meses también se desvincularán. Esto es excelente para las columnas de datos, pero no para las columnas de atributos/dimensiones.
Como en nuestro escenario, es más probable que agreguemos más meses, por lo que Desvincular otras columnas es la mejor opción.
Ahora, ordenemos la consulta con las siguientes transformaciones:
- Cambie el encabezado de la columna Atributo a Meses .
- Cambie el tipo de datos de cada columna para que coincida con los datos dentro de la columna.
Haga clic en Cerrar Cargar para enviar los datos a Excel. La hoja de trabajo se verá así:
A partir de esto, podemos usar fácilmente fórmulas o tablas dinámicas para crear cualquier vista que queramos. Este es el poder que nos brinda desvincular nuestros datos.
Desvincular con múltiples columnas
Ahora es el momento del Ejemplo 2. Los datos de origen se ven así:
En lugar de una columna para cada mes, ahora tenemos dos, que muestran los valores de ventas y las unidades de ventas.
El formato más útil es tener los nombres de los meses en una sola columna con el Valor y las Unidades como columnas separadas.
Los pasos iniciales son los mismos que en el Ejemplo 1:
- Convierta los datos en una tabla (use la primera fila como encabezado y excluya el total del rango).
- Dale a la tabla un nombre significativo.
- Haga clic en Datos de tabla/rango para cargar los datos en Power Query.
- Solo para estar seguro, elimine los pasos de tipo de cambio que Power Query creó automáticamente.
La ventana de vista previa debería ser la siguiente:
Seleccione las columnas Cliente y Producto y haga clic en Transformar columnas desvinculadas (desplegable) Desvincular otras columnas
Ahora necesitamos hacer bastantes transformaciones; Estos dependen de su conjunto de datos específico. Por lo tanto, es posible que las instrucciones siguientes no coincidan con su situación exacta.
En esta etapa, nuestros datos están sobrenormalizados. Tenemos Valores y Unidades en la misma columna, pero son unidades de datos diferentes.
Necesitamos retroceder para separar los Valores y las Unidades.
- Seleccione la columna Atributo y divida la columna en las partes que necesitamos.
Para nuestro ejemplo, necesitamos dividir por el carácter de espacio. Haga clic en Transformar Dividir columna Dividir por delimitador y luego aplique la configuración en la imagen a continuación. - La columna Atributo se divide en dos columnas separadas.
- Seleccione la columna que muestra Valores y Unidades (probablemente llamada Attribute.2 ), luego haga clic en Transformar columna dinámica . En la ventana Columna dinámica, seleccione la columna que contiene los números y luego haga clic en Aceptar .
- Cambie los nombres de la columna Attributes.1 a Months .
- Establezca el tipo de datos para cada columna.
Finalmente, haga clic en Cerrar carga para enviar los datos a Excel.
Los datos no dinamizados en Excel ahora se ven así:
¡Perfecto!
Desvincular con varias filas de encabezado
Ahora es el momento de nuestro ejemplo final. Todo es igual que en el Ejemplo 2, excepto los encabezados de la tabla. Tenemos dos filas de encabezado, la primera muestra Meses y la segunda muestra Valor o Unidades.
Separar los encabezados en varias filas es un formato de presentación con el que es muy difícil trabajar.
Las tablas de Excel solo pueden tener una única fila de encabezado. Como tenemos dos filas de encabezado, usemos un rango con nombre en lugar de una tabla.
Seleccione todo el rango e ingrese el nombre SalesData en el cuadro de nombre.
Luego, con el rango con nombre seleccionado, haga clic en Datos de tabla/rango .
Una vez que los datos se hayan cargado en Power Query, elimine los pasos aplicados automáticamente, como Encabezados promocionados y Tipo modificado . Solo queremos el paso Fuente .
La ventana de vista previa muestra lo siguiente:
Esta visión nos plantea un problema; no muestra el mes en cada columna. Si intentamos desvincular, desvinculará el valor nulo. Entonces, primero debemos usar la transformación Rellenar transversalmente... pero espera... ¡no hay ninguna!
En su lugar, transponemos los datos, aplicamos la transformación Rellenar hacia abajo y luego los volvemos a transponer.
- Haga clic en Transformar Transponer
- Seleccione Columna1 , haga clic en Transformar relleno (menú desplegable) Abajo
- Seleccione Columna1 y Columna2
- Haga clic en Transformar columnas de combinación
- En la ventana Combinar columnas , elija un separador que no esté en el texto de las dos columnas (he seleccionado una tubería ( | ))
- Haga clic en Transformar Transponer para devolver los datos a su formato anterior.
- Seleccione Columna 1 y Columna 2 .
- Haga clic en Transformar Reemplazar valores . Reemplace el | con [en blanco]. Esto elimina el | carácter de las columnas Cliente y Producto que agregamos en un paso anterior
- Ahora promueva la primera fila como encabezado haciendo clic en Transformar Usar primera fila como encabezados
Después de todas esas transformaciones, la ventana de vista previa se verá así:
Ahora tiene un formato similar al que teníamos en el ejemplo 2. Entonces, para completar este ejemplo, siga todos los pasos de transformación del ejemplo 2.
Conclusión
Aprender a desvincular en Excel es fundamental para su capacidad de trabajar de manera eficiente. Si los datos tienen el diseño correcto, es fácil trabajar con ellos y evita la complejidad. Power Query nos brinda las herramientas para desvincular datos en diversas situaciones.
Leer más publicaciones en esta serie
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta