Los datos de la hoja de cálculo nunca llegan limpios. Entre los títulos faltantes, el formato inconsistente y las celdas fusionadas, solía ocurrir horas luchando con conjuntos de datos desordenados. Luego me topé con la consulta de potencia, y de repente esas aburridas tareas de aseo se sintieron menos como castigo y más como interruptores.

Power Query hace que la aseo de datos sea manejable

Power Query es el motor de transformación de datos incorporado de Excel que automatiza tareas de datos tediosos. En punto de quitar manualmente cada celda, enseña la consulta de potencia lo que desea una vez, y aplica esos mismos pasos a cualquier conjunto de datos. Puede eliminar duplicados, dividir columnas, cambiar los tipos de datos y manejar los títulos faltantes con solo unos pocos clics.

Tiene un enfoque paso a paso y una interfaz visual, lo que hace que sea manejable de seguir. Cada batalla que realiza se registra como un paso que se puede modificar o eliminar más tarde. Principalmente, está manipulando datos, no escribiendo código, aunque genera un código m detrás de secuencia si tiene curiosidad.

Power Query se conecta a prácticamente cualquier fuente de datos, incluidos archivos de Excel, documentos CSV, bases de datos, páginas web e incluso servicios en la nubarrón. Simplemente agregue un archivo a la misma carpeta, y Power Query obtendrá automáticamente y ordenará esa hoja de Excel desordenada.

Cómo configurar PowerQuery en Excel

Power Query se integra en Excel 2016 y versiones posteriores. Por lo tanto, no necesita una instalación separada.

Eche un vistazo a la hoja de cálculo de ventas de los siguientes productos, es un desastre. Tiene formatos de época mixta, nombres de productos inconsistentes, títulos faltantes y espacios adicionales en todas partes. Este es el tipo de conjunto de datos que me hace querer dejar de Excel por completo.

Pero es manejable arreglarlo con una consulta de potencia. Aquí le mostramos cómo configurar su primera transformación:

1. Seleccione su rango de datos o haga clic en cualquier punto en el interior de su conjunto de datos.

2. Ir a Datos, Luego haga clic De la mesa/rango.

3. Excel detectará sus límites de datos y creará una tabla.

4. Hacer clic DE ACUERDO Para inaugurar el editor de consultas de energía.

5. Haga sus transformaciones utilizando las herramientas de cinta.

6. Hacer clic Cerrar y cargar Para aplicar los cambios de nuevo a Excel.

El editor de consultas de potencia abre en una ventana separada. Este entorno ocasional le permite constatar sin afectar sus datos originales, piense en ellos como una caja de arena para la aseo de datos.

Sus transformaciones se guardan como consultas en el panel de consultas. Estas consultas son reutilizables y refrescables. Cuando lleguen nuevos datos, simplemente haga clic derecho y seleccione Refrescar Para aplicar los mismos pasos de aseo automáticamente.

Aproximación esta automatización a mano cuando se construye fórmulas de búsqueda de rayos rápidos, ya que los datos limpios y consistentes hacen que las relaciones de mesa funcionen correctamente en punto de romper en diferencias de formato menores.

Estos pasos de aseo manejan la mayoría de los problemas de datos

Luego de cargar los datos en el editor de consultas de potencia, el ulterior paso es limpiarlos. Abordemos el archivo de datos de ventas utilizando las transformaciones de Power Query. Los siguientes pasos resuelven el 90% de los problemas de datos comunes.

Los encabezados fusionados rompen todo. La consulta de potencia no puede detectar correctamente los tipos de columnas con “información de ventas” que alpargata múltiples columnas. Siga estos pasos para solucionarlo.

1. Seleccione la fila de encabezado fusionado en el editor de consultas de potencia.

2. Haga clic derecho y elija Retire las filas> Eliminar las filas superiores.

3. Ingresar 1 Para eliminar la fila de encabezado fusionado.

4. Hacer clic DE ACUERDO. Ahora los encabezados de la columna auténtico están posicionados correctamente.

Paso 2: Estandarizar los formatos de época

Nuestra hoja de cálculo tiene ocho formatos de época diferentes, incluidas entradas problemáticas como “16/01/2024”. La consulta de potencia predeterminada al formato de época de EE. UU. (MM/DD/YYYY), por lo que “16/01/2024” desencadena un error ya que 16 no puede ser un mes válido. Desafortunadamente, tendrá que reemplazar estos títulos manualmente o usar las funciones de época de Excel.

Para estandarizar las fechas:

1. Seleccione la columna Término.

2. Ir a Variar> Tipo de datos> Término.

3. La consulta de potencia detecta la mayoría de los formatos automáticamente, incluidos “15 de enero de 2024” y “15-enero-24”.

4. Verifique la paisaje previa para confirmar todas las fechas convertidas correctamente.

Cuando se prostitución de formatos de época internacionales mixtos, siempre previsualice sus transformaciones. La detección cibernética de Power Query funciona correctamente para formatos inequívocos, pero falta en DD/mm/aaa yyy cuando los títulos del día exceden 12.

Puede hacer esto haciendo clic derecho en la columna, seleccionando Tipo de cambio> Uso de localesluego eligiendo Término y el punto deseado. Esto es más preciso que solo durante la “importación de datos iniciales”.

Paso 3: Desistencias de texto de aseo de texto

Los nombres de productos como “Surface Latop” deben ser capitalizados, y “iPhone 15”, “iPhone15” y “iPhone-15” necesitan estandarización. Ahí es donde verdaderamente brilla la consulta de poder:

1. Seleccione la columna Nombre del producto.

2. Ir a Variar> Formato y seleccione el caso adecuado para fijar la capitalización.

3. Entonces, de nuevo, haga clic Variar> Reemplazar títulos para estandarizar las variaciones.

Los espacios liderantes y finales en los nombres de los representantes de ventas crean fallas de búsqueda, uno de esos errores de Excel que pueden costarle tiempo durante el observación. Para arreglar eso:

1. Seleccione la columna de representantes de ventas.

2. Ir a Variar> formato> retazo.

Los espacios desaparecen de entradas como “Sarah Johnson” y “Emma Taylor”. Este simple clic corrige problemas de extendido que de otro modo romperían las tablas dinámicas y las fórmulas Vlookup.

Paso 5: manejar los títulos faltantes de guisa inteligente

Las celdas vacías en la categoría y las unidades vendidas columnas necesitan atención. Power Query ofrece varios enfoques.

Para categorías:

1. Seleccione la columna de categoría.

2. Entonces ve a Variar> Reemplazar títulos.

3. Reemplace los títulos nulos con “Uncategorized” o deje en blanco para la revisión manual.

Para columnas numéricas como las unidades vendidas, reemplazar nulos con cero podría sesgar promedios. Considere marcarlos como “desidia de datos” en su punto.

Paso 6: estandarizar los formatos de divisas

Los títulos de ingresos aparecen como “$ 1,200”, “1200” y “$ 1.2k”, cada uno requiere un manejo diferente.

1. Seleccione la columna de ingresos.

2. Usar Reemplazar títulos para convertir “$ 1.2k” a “1200”.

3. Entonces ve a Variar> Tipo de datos> Monedaque maneja automáticamente los signos y comas del dólar.

4. Pinta previa de los resultados para respaldar la conversión adecuada.

La sigla “K” necesita reemplazo manual ya que la consulta de potencia no reconoce automáticamente esta notación.

Paso 7: Crear categorías consistentes

“Electrónica”, “electrónica” y “electrónica” deberían ser idénticas. Las funciones de texto de Power Query resuelven esto rápidamente.

1. Seleccione la columna de categoría.

2. Hacer clic Variar> Formato y seleccione el caso adecuado para fijar la capitalización.

Estos siete pasos manejan la mayoría de los escenarios de aseo de datos. Estas transformaciones se convierten en pasos repetibles. Solo tiene que aplicar el mismo proceso a los datos de ventas desordenados del próximo mes con una sola puesta al día.

Cada transformación aparece en el panel de pasos aplicados. Puede modificar, eliminar o reordenar pasos sin comenzar de nuevo. Esta flexibilidad hace que la consulta de potencia sea perdonar cuando necesite ajustar su proceso de aseo.

La consulta de potencia no resolverá todos los problemas de datos que encuentre, pero maneja la mayoría de las tareas de aseo repetitivas que consumen horas de trabajo manual. Claro, la curva de estudios existe si trabaja en hojas de trabajo complejas, pero los ahorros de tiempo se hacen evidentes rápidamente. Ya sea que esté tratando con informes mensuales o conjuntos de datos únicos, estas técnicas de transformación proporcionan una almohadilla sólida para un observación de datos más íntegro y confiable.