Ik heb Excel altijd gebruikt voor snelle berekeningen en het maken van eenvoudige tabellen. Maar afgezien van gangbare formules en basistechnieken voor gegevensmanipulatie, voelde ik nooit de behoefte om extra Excel-functies te leren – totdat mijn projecten complexer werden.
Snelle links
Het probleem dat mij uiteindelijk deed opletten
Door verschillende marktfactoren en invoerrechten is de aankoop van computeronderdelen in mijn regio vaak duurder dan in de Verenigde Staten. Ik wilde weten hoeveel meer ik voor dezelfde onderdelen betaalde en of het beter was om rechtstreeks bij Amazon of Newegg te bestellen in plaats van bij lokale retailers. Dus verzamelde ik een paar maanden lang prijsgegevens voor belangrijke computeronderdelen (CPU's, GPU's en RAM) die lokale winkels doorgaans importeren. Simpel trackingproject, toch? Fout.
Ik had al snel een enorme data-chaos. Elke retailer exporteerde zijn gegevens met verschillende opmaakconventies, waardoor het bijna onmogelijk was om de bestanden samen te voegen. Amazon gaf de data aan in MM/DD/JJJJ, Newegg gebruikte JJJJMMDD en Shopee (mijn lokale winkel) gebruikte DD-MM-JJJJ.
De inconsistenties hielden daar niet op. Kolomnamen varieerden enorm. Newegg labelde prijzen als "retail_price", terwijl Amazon "unit_price_usd" gebruikte en Shopee koos voor "price_php". De prijsopmaak was eveneens problematisch: in sommige bestanden werd "₱18,600" weergegeven, inclusief valutasymbolen, terwijl andere gewone getallen zoals "320" lieten zien. Zelfs merknamen waren niet consistent en werden in verschillende bestanden weergegeven als "gigabyte", "GIGABYTE INC." of "Gigabyte Tech" voor dezelfde fabrikant.
Het handmatig opschonen en samenvoegen van deze data kostte me al uren. Ik moest tussen bestanden kopiëren en plakken, inconsistente waarden zoeken en vervangen, en lege rijen één voor één verwijderen. Het converteren van PHP naar USD voor prijsvergelijkingen betekende dat ik constant naar een ander scherm moest kijken voor wisselkoersen. Al met al was het werk saai en foutgevoelig, en ik gaf het bijna op.
Toen dacht ik er eindelijk aan om een van de functies te gebruiken waar Excel-liefhebbers het altijd over hebben: Power Query. Veel andere krachtige functies die Excel biedtMaar ik had gehoord dat Power Query de perfecte tool was voor mijn specifieke probleem. Dus na het bekijken van een paar YouTube-tutorials besefte ik meteen hoeveel tijd ik kon besparen zodra ik de Power Query Editor ging gebruiken om alle rommelige data die ik van internet had verzameld op te ruimen. Met Power Query kan ik nu eenvoudig data uit verschillende bronnen importeren, converteren naar een gestandaardiseerd formaat en efficiënt analyseren, wat me kostbare tijd en moeite bespaart bij mijn projecten voor de analyse van de prijs van computercomponenten.
Hoe gebruik ik Power Query om ongestructureerde gegevens op te schonen?
Na een tijdje koos ik voor een eenvoudig, stapsgewijs proces in de Power Query-editor. Hier leest u hoe ik mijn rommelige CSV-exporten heb opgeruimd en omgezet in een consistente, overzichtelijke spreadsheet.
Eerst importeerde ik mijn gegevens in de Power Query-editor door een lege werkmap te openen en op Data Selecteer in het lint Van tekst/CSV.Toen selecteerde ik mijn CSV-bestand en klikte op Transformeer gegevens Om het te openen met de Power Query-editor.
Ik begon met het aanpassen van de datumkolom. Omdat ik gegevens verzamelde uit twee bronnen met een tijdsverschil van 12 uur, moest ik de datums verenigen. Dat bleek vrij eenvoudig. Ik definieerde de kolom Datum, klik met de rechtermuisknop om het contextmenu te openen en kies Type wijzigen > Landinstellingen gebruikenIn het pop-upmenu stel ik het type in op Datum en geïdentificeerd Engels (Verenigde Staten) Om een consistente opmaak te garanderen, herkent Power Query automatisch verschillende notaties, zoals MM/DD/JJJJ, JJJJ/MM/DD, en variabelen die symbolen gebruiken zoals DD-MM-JJ, en verenigt deze vervolgens allemaal in één datumnotatie.
Nu ik de datumnotatie had aangepast, hoefde ik alleen nog de kolom op te schonen. هناك Verschillende manieren om een Excel-spreadsheet op te schonenMaar omdat alle fouten onjuiste vermeldingen waren die mijn scraper had gegenereerd, heb ik er gewoon voor gekozen om een filter te gebruiken. Verwijder fouten Om deze vermeldingen te verwijderen. Met deze stap werden null-waarden en overige problematische gegevens die niet goed waren vastgelegd, verwijderd. Zo kreeg ik schone, consistente datums in al mijn bestanden.
Vervolgens heb ik de rommel rond de merknamen aangepakt met een functie. Waarden vervangenZoals eerder heb ik de doelkolom geselecteerd, vervolgens met de rechtermuisknop geklikt om het contextmenu te openen en geselecteerd Waarden vervangenVoer in het pop-upvenster de inconsistente waarde in het veld in. Waarde om te vinden en mijn standaardwaarde in veld Vervangen met veld.
Ik deed dit nog twee keer en converteerde uiteindelijk al die "gigabyte"- en "GIGABTYE Inc."-vermeldingen naar één consistente "GIGABYTE"-vermelding in al mijn bestanden. Ik deed hetzelfde met AMD, en nu gebruikt de volledige merkkolom voor GPU's standaardmerknamen.
Power Query: Hoe het mij uren werk bespaarde
Een van de redenen waarom ik Power Query vermeed, was dat ik dacht dat het weer een complexe functie zou zijn die lang zou duren om te leren. Maar het bleek veel eenvoudiger dan ik had verwacht. In plaats van eindeloos zoeken en vervangen, kan ik Power Query gebruiken om snel en automatisch gegevens uit mijn gegevensverzamelingstools op te schonen.
Wat me het meest verbaasde aan Power Query, is dat elke opdracht die ik uitvoerde werd vastgelegd en steeds opnieuw kon worden herhaald. Dit geeft je in feite een geautomatiseerd opschoonscript dat rommelige CSV-bestanden kan omzetten in schone, overzichtelijke spreadsheets – perfect als je aan een Maak aangepaste datasets met behulp van webscraping, omdat deze tools vaak onzuivere gegevens opleveren.
Voor iedereen die te maken heeft met terugkerende dataopschoningen, inconsistente formaten of meerdere gegevensbronnen, transformeert Power Query deze lasten in een eenvoudig, geautomatiseerd proces. In plaats van uren per week te besteden aan handmatige oplossingen, kunt u gewoon op "Vernieuwen" klikken en beginnen met analyseren. Het is een Excel-functie die ik al lang geleden had willen implementeren. Zodra u de kracht van een geautomatiseerd, herhaalbaar opschoonscript ervaart, is er geen weg meer terug. Power Query is een krachtige tool die tijd en moeite bespaart bij dataverwerking en biedt geavanceerde oplossingen voor effectieve dataopschoning en -transformatie.
