Siempre he usado Excel para cálculos rápidos y tablas simples. Pero por separado de las fórmulas comunes y las técnicas básicas de manipulación de datos, nunca sentí la carencia de estudiar funcionalidades adicionales de Excel, hasta que mis proyectos comenzaron a ser complejos.

El problema que finalmente me hizo prestar atención

Correcto a varios factores de mercado y aranceles de importación, comprar componentes de PC en mi ámbito a menudo es más costoso que en los Estados Unidos. Quería vigilar cuánto más estaba pagando por los mismos componentes y si sería mejor ordenar directamente desde Amazon o Newegg en circunscripción de los minoristas locales. Por lo tanto, recopilé unos meses de fijación de datos de fijación de precios en componentes secreto de PC (CPU, GPU, RAM) que las tiendas locales generalmente importan. Esquema de seguimiento simple, ¿verdad? Erróneo.

Rápidamente terminé con un completo desastre de datos. Cada minorista exportó su información utilizando diferentes convenciones de formato que hicieron que la combinación de archivos fuera casi impracticable. Amazon proporcionó fechas en formato MM/DD/A yyyy, y Newegg fue con A yyyyMMDD, y Shopee (mi tienda específico) usó DD-MM-YYYYY.

Las inconsistencias no se detuvieron allí. Los nombres de la columna variaron salvajemente. Newegg etiquetó los precios como “minorista_price”, mientras que Amazon usó “unit_price_usd”, y Shopee eligió “Price_Php”. El formato de precios fue igualmente problemático, con algunos archivos que muestran “₱ 18,600”, incluidos los símbolos de divisas, mientras que otros mostraron números simples como “320”. Incluso las marcas carecían de consistencia, apareciendo como “Gigabyte”, “Gigabyte Inc.” O “Gigabyte Tech” para el mismo fabricante en diferentes archivos.

La pulcritud y combinación manual de estos datos ya me estaba tomando horas. Tuve que copiar y pegar entre archivos, encontrar y reemplazar títulos inconsistentes, y eliminar filas en blanco una por una. La conversión de PHP a USD para las comparaciones de precios significaba mirar constantemente a otro preceptor de tipos de cambio. En normal, fue un trabajo tedioso y propenso a errores lo que casi me hizo rendirme.

Fue entonces cuando finalmente consideré usar la única característica de la que los entusiastas de Excel siempre hablan: la consulta de potencia. Hay varias otras características de potencia que proporciona Excel, pero escuché que Power Query era la utensilio perfecta para mi problema específico. Entonces, a posteriori de ver algunos tutoriales de YouTube, inmediatamente me di cuenta de cuánto tiempo podía racionar una vez que comencé a usar Power Query Editor para levantar todos los datos desordenados que había raspado de Internet.

Cómo uso la consulta de energía para levantar los datos sucios

En poco tiempo, me decidí por un simple proceso paso a paso en el editor de consultas de potencia. Así es exactamente cómo limpié esas desordenadas exportaciones de CSV y las convertí en una hoja de cálculo consistente y admisiblemente estructurada.

Primero, importé mis datos en el editor de consultas de potencia abriendo un ejemplar de trabajo en blanco, haciendo clic en Datos en la cinta y seleccionando De texto/CSV. Luego seleccioné mi archivo CSV y hice clic Transfigurar datos Para abrirlo con Power Query Editor.

Comencé arreglando la columna de término. Como estaba recopilando datos de dos fuentes con plazos que estaban separados con 12 horas, necesitaba estandarizar las fechas. Resultó ser increíblemente simple. Seleccioné el Data columna, haga clic con el brote derecho para desobstruir el menú contextual y eligió Tipo de cambio> Uso de locales. En el menú emergente, configuré el tipo en Data y seleccionado Inglés (Estados Unidos) para avalar un formato constante. La consulta de potencia luego reconoció automáticamente diferentes formatos, como MM/DD/YYYY, YYYY/MM/DD, y variantes que usan símbolos como DD-MMM-YY, luego los estandarizaron a todos a un formato de una sola término.

Ahora que el formato de término se solucionó, solo necesitaba levantar la columna. Hay diferentes formas de levantar una hoja de cálculo de Excel, pero poliedro que todos los errores son malas entradas causadas por mi utensilio de raspado, simplemente opté por usar el Eliminar errores Filtrar para eliminar esas entradas. Este paso eliminó los títulos nulos y los datos problemáticos restantes que no se registraron correctamente, dejándome con fechas limpias y consistentes en todos mis archivos.

A continuación, abordé el caos de la marca usando el Reemplazar títulos función. Al igual que anteriormente, seleccioné la columna de destino, luego haga clic con el brote derecho para desobstruir el menú contextual y seleccionado Reemplazar títulos. En la ventana emergente, ingresé el valía inconsistente en el Valencia para encontrar campo y mi valía unificado en el Reemplazar con campo.

Lo hice unas dos veces más y finalmente hice todos esos “Gigabyte” y “Gigabtye Inc.” en un solo “gigabyte” consistente en todos mis archivos. Hice lo mismo con AMD y ahora toda la columna de marca para GPU usa marcas unificado.

Por postrer, necesitaba estandarizar el Precio columna. Esto fue un poco complicado porque muchas de las entradas eran solo números, sin ninguna indicación de si estaban en dólares estadounidenses o pesos filipinos, lo que dificultó la conversión. Felizmente, el Acumular La columna estaba calibrado a su banda, lo que me permitió convertir fácilmente todos los precios del pH de Shoppe en USD.

La forma en que manejé este problema era usar primero Reemplazar títulos Para eliminar todos los $, ₱, USD y PHP de la columna de precios. Luego, convertí los títulos en decimales usando Tipo de cambio > Fracción. Con una columna limpia que contiene solo decimales, creé una nueva columna haciendo clic en Columna (cinta)> Columna personalizada y lo llamó “precio USD”. Luego entré en la próximo fórmula:

if (Store) = "Shopee PH"
then Number.Round((Price) / 55, 2)
else Number.Round((Price), 2)

Luego zumbar Bueno. Esto convirtió todos los precios del pH de Shopee en USD para una obvio comparación.

La fórmula está escrita en el Power Query M Fórmula Verbo. Es un habla de secuencias de comandos sobrado sencillo con un concepto de núcleo simple que es obvio de comprender. No necesita estudiar todo el idioma para usarlo de modo efectiva, no lo hice. En cambio, solo lo uso como narración cada vez que necesito construir mis propias fórmulas.

Ahora que todo está arreglado, simplemente cerré el editor de consultas de potencia y elegí Amparar Para economizar todos los cambios en el ejemplar de trabajo creado.

La consulta de poder me ahorró horas de mi tiempo

Una razón por la que evité la consulta de poder fue que supuse que sería otra característica complicada que tomaría demasiado tiempo para estudiar. Pero resultó ser mucho más obvio de lo que esperaba. En circunscripción de ejecutar comandos de búsqueda y reemplazo interminables, podría usar Power Query para levantar rápida y automáticamente los datos de mis herramientas de raspado.

Lo que me sorprendió aún más sobre Power Query fue que cada comando que hice fue fotograbado y podría repetirse una y otra vez. Esto esencialmente le brinda un script de pulcritud automatizado que puede mudar archivos CSV desordenados en hojas de cálculo limpias y estructuradas, perfecta si está creando conjuntos de datos personalizados con raspado web, ya que esas herramientas a menudo generan datos sucios.

Para cualquier persona que se ocupe de la pulcritud de datos repetitivos, formatos inconsistentes o múltiples fuentes de datos, la consulta de potencia convierte esas cargas en un proceso simple y automatizado. En circunscripción de tallar horas cada semana para soluciones manuales, presiona la puesta al día y comienza a analizar. Es la característica de Excel que desearía poseer aceptado hace mucho tiempo. Una vez que experimente el poder de un script de pulcritud automatizado y reproducible, no hay reverso a espaldas.