Hoe Power Query in Excel te gebruiken?
Excel Power Query wordt gebruikt voor het doorzoeken van gegevensbronnen, het maken van verbindingen met gegevensbronnen en het vervolgens vormgeven van de gegevens volgens onze analysebehoefte. Zodra we klaar zijn met het vormgeven van de gegevens volgens onze behoeften, kunnen we ook onze bevindingen delen en verschillende rapporten maken met meer zoekopdrachten.
Stappen
In wezen zijn er 4 stappen, en de volgorde van deze 4 stappen in Power Query is als volgt:
- Connect: We maken eerst verbinding met de gegevens, die zich ergens, in de cloud, in service of lokaal kunnen bevinden.
- Transformeren: de tweede stap zou zijn om de vorm van de gegevens te veranderen volgens de vereisten van de gebruiker.
- Combineren: in deze stap voeren we enkele transformatie- en aggregatiestappen uit en combineren we gegevens uit beide bronnen om een gecombineerd rapport te produceren.
- Beheren: hiermee worden kolommen in een query samengevoegd en toegevoegd met overeenkomende kolommen in andere query's in de werkmap.
Er zijn veel superkrachtige functies van Excel Power Query.
Stel dat we aankoopgegevens van de afgelopen 15 jaar hebben in 180 bestanden. Nu zou het management van een organisatie de cijfers moeten consolideren voordat ze worden geanalyseerd. Het management kan een van de volgende methoden gebruiken:
- Ze zouden alle bestanden openen en ze in één bestand kopiëren en plakken.
- Aan de andere kant kunnen ze een verstandige oplossing gebruiken, namelijk het toepassen van formules, aangezien dit foutgevoelig is.
Welke methode ze ook kiezen, het bevat veel handmatig werk en na een paar maanden zouden er nieuwe verkoopgegevens zijn voor de toegevoegde duur. Ze zullen dezelfde oefening opnieuw moeten doen.
Power Query kan hen echter helpen dit vervelende en repetitieve werk niet te doen. Laten we deze Excel Power-query begrijpen met een voorbeeld.
Voorbeeld
Stel dat we tekstbestanden hebben in een map met verkoopgegevens, en we willen die gegevens in ons Excel-bestand krijgen.
Zoals we in de onderstaande afbeelding kunnen zien, hebben we twee soorten bestanden in de map, maar we willen de gegevens van alleen tekstbestanden in het Excel-bestand krijgen.
Om hetzelfde te doen, zouden de stappen zijn:
Stap 1: Eerst moeten we de gegevens in de Power Query ophalen, zodat we de vereiste wijzigingen in de gegevens kunnen aanbrengen om die in een Excel-bestand te importeren.
Om hetzelfde te doen, zullen we kiezen voor “Van Folder” optie in het “From File” menu na het klikken op de opdrachtregel “Get Data” van de “Get & Transform” groep in de “Data” tab.
Stap 2: Selecteer de locatie van de map door te bladeren.
Klik op 'OK'
Stap 3: Er wordt een dialoogvenster geopend met de lijst voor alle bestanden in de geselecteerde map met de kolomkoppen als 'Inhoud', 'Naam', 'Extensie', 'Toegangsdatum', 'Datum gewijzigd', 'Aanmaakdatum', 'Kenmerken' en 'Mappad'.
Er zijn 3 opties, namelijk Combineren , Laden en Gegevens transformeren .
- Combineren : Deze optie wordt gebruikt om naar een scherm te gaan waar we kunnen kiezen welke gegevens we willen combineren. De bewerkingsstap wordt overgeslagen voor deze optie en geeft ons geen controle over welke bestanden we moeten combineren. De functie Combineren neemt elk bestand in de map om te consolideren, wat tot fouten kan leiden.
- Laden: deze optie laadt gewoon de tabel zoals hierboven weergegeven in de afbeelding in het Excel-werkblad in plaats van de feitelijke gegevens in de bestanden.
- Gegevens transformeren: In tegenstelling tot de opdracht 'Combineren' , kunnen we, als we deze opdracht gebruiken, kiezen welke bestanden we willen combineren, dwz we kunnen slechts één type bestand (dezelfde extensie) combineren.
Net als in ons geval willen we alleen tekstbestanden (.txt) combineren; we zullen het "Transform Data" commando kiezen.
We kunnen "Applied Steps" zien aan de rechterkant van het venster. Voorlopig is er maar een enkele stap gedaan, namelijk het overnemen van bestandsdetails uit de map.
Stap 4: Er is een kolom met de naam 'Extensie' waar we kunnen zien dat de waarden in de kolom in beide gevallen worden geschreven, dwz hoofdletters en kleine letters.
We moeten echter alle waarden naar kleine letters converteren, omdat het filter onderscheid maakt tussen beide. Om hetzelfde te doen, moeten we de kolom selecteren en vervolgens "Kleine letters" kiezen in het menu van de opdracht "Opmaak" .
Stap 5: We filteren de gegevens met behulp van de kolom 'Extensie' voor tekstbestanden.
Stap 6: We moeten de gegevens voor beide tekstbestanden nu combineren met de eerste kolom 'Inhoud'. We klikken op het pictogram aan de rechterkant van de kolomnaam.
Stap 7: Een dialoogvenster met de titel 'Bestanden combineren' wordt geopend waarin we het scheidingsteken als 'Tab' moeten selecteren voor tekstbestanden (bestanden met de extensie '.txt') en de basis kunnen kiezen voor datatype-detectie. En klik op 'OK'.
Nadat we op 'OK' hebben geklikt , krijgen we de gecombineerde gegevens van tekstbestanden in het 'Power Query'- venster.
We kunnen het gegevenstype van de kolommen naar wens wijzigen. Voor de kolom 'Opbrengst' wijzigen we het gegevenstype in 'Valuta'.
We kunnen de stappen zien die op de gegevens zijn toegepast met behulp van een stroomvraag aan de rechterkant van het venster.
Nadat we alle vereiste wijzigingen in de gegevens hebben aangebracht, kunnen we de gegevens in een Excel-werkblad laden met de opdracht 'Sluiten en laden naar' onder de groep 'Sluiten' op het tabblad 'Home' .
We moeten kiezen of we de gegevens als een tabel of verbinding willen laden. Klik vervolgens op 'OK'.
Nu kunnen we de gegevens als een tabel in het werkblad zien.
En het paneel 'Werkboekquery's' aan de rechterkant, dat we kunnen gebruiken voor het bewerken, dupliceren, samenvoegen, toevoegen van de query's en voor vele andere doeleinden.
Excel Power Query is erg handig omdat we kunnen zien dat binnen een paar minuten 601.612 rijen zijn geladen.
Dingen om te onthouden
- Power Query verandert de oorspronkelijke brongegevens niet. In plaats van de oorspronkelijke brongegevens te wijzigen, registreert het elke stap die door de gebruiker wordt genomen tijdens het verbinden of transformeren van de gegevens, en zodra de gebruiker klaar is met het vormgeven van de gegevens, neemt het de verfijnde gegevensset en brengt deze naar de werkmap.
- Power Query is hoofdlettergevoelig.
- Bij het consolideren van de bestanden in de opgegeven map, moeten we ervoor zorgen dat we de kolom 'Extensie' gebruiken, en we moeten tijdelijke bestanden uitsluiten (met de extensie '.tmp' en de naam van deze bestanden begint met '~' teken) als Power Query kan deze bestanden ook importeren.
