Excel power-tutorial‌ query (ophalen en transformeren)

Als u een van die mensen bent die veel met data werkt, u kunt iedereen zijn ( accountant , HR, data-analist, enz.), dan kan PowerQuery uw krachtige tool zijn.

Laat ik beginnen met de achtervolging: Power Query is een van de geavanceerde Excel-vaardigheden die u moet leren en in deze zelfstudie gaat u PowerQuery in detail verkennen en leert u hoe u er gegevens mee kunt transformeren.

Laten we beginnen.

Wat is Excel PowerQuery

Power Query is een Excel-invoegtoepassing die u voor ETL kunt gebruiken. Dit betekent dat u gegevens uit verschillende bronnen kunt extraheren , transformeren en vervolgens in de spreadsheet kunt laden . Je kunt zeggen dat POWER QUERY een machine voor het opschonen van gegevens is, omdat deze alle mogelijkheden heeft om gegevens te transformeren. Het is realtime en registreert elke stap die u zet.

Waarom zou u Power Query (voordelen) gebruiken?

Als u deze vraag in gedachten heeft, is hier mijn antwoord voor u:

• Verschillende gegevensbronnen : u kunt gegevens in een krachtige query-editor laden vanuit verschillende gegevensbronnen, zoals CSV , TXT, JSON, enz.
• Gegevens eenvoudig transformeren: Normaal gesproken gebruikt u formules en draaitabellen voor gegevenstransformaties, maar met POWER QUERY kunt u met slechts een paar klikken veel doen.
• Het is realtime: schrijf één keer een query en u kunt deze elke keer vernieuwen als er een wijziging in de gegevens plaatsvindt, en de nieuwe gegevens die u hebt bijgewerkt, worden getransformeerd.

Laat me een voorbeeld delen:

Stel je voor dat je 100 Excel-bestanden hebt met gegevens uit 100 steden en nu wil je baas dat je een rapport maakt met alle gegevens uit die 100 bestanden. Oké, als je besluit elk bestand handmatig te openen en de gegevens uit deze bestanden te kopiëren en te plakken, heb je daar minstens een uur voor nodig.

Maar met Power Query kunt u dit binnen enkele minuten doen. Voel je je opgewonden? GOED.

Later in deze zelfstudie leert u Power Query gebruiken met veel voorbeelden, maar eerst moet u het concept ervan begrijpen.

Het concept van de stroomvraag

Om Power Query te leren, moet u het concept ervan begrijpen, dat in 3 stappen werkt:

1. Gegevens ophalen

Met Power Query kunt u gegevens uit verschillende bronnen halen, zoals internet, CSV, tekstbestanden, meerdere werkmappen uit één map en vele andere bronnen waar we gegevens kunnen opslaan.

2. Transformeer de gegevens

Nadat u gegevens in de powerquery hebt ingevoerd, beschikt u over een hele reeks opties die u kunt gebruiken om deze te transformeren en op te schonen. Er worden query’s gemaakt voor alle stappen die u uitvoert (de ene stap na de andere).

3. Gegevens laden

Vanuit de Geavanceerde Query-editor kunt u de getransformeerde gegevens in het werkblad laden, of u kunt rechtstreeks een draaitabel of draaigrafiek maken of een verbinding met alleen gegevens maken.

Waar is Power Query (hoe te installeren)?

Hieronder ziet u hoe u Power Query Access in verschillende versies van Microsoft Excel installeert.

Exel 2007

Als u Excel 2007 gebruikt, spijt het mij dat PQ niet beschikbaar is voor deze versie, dus u moet upgraden naar de latest version van Excel (Excel voor Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel2010 en Excel2013

Voor 2010 en 2013 moet u apart een add-in installeren die u via deze link kunt downloaden. Eenmaal geïnstalleerd krijgt u een nieuw tabblad in het Excel-lint, zoals hieronder:

• Download eerst de invoegtoepassing vanaf hier (officiële website van Microsoft).
• Zodra het bestand is gedownload, opent u het en volgt u de instructies.
• Daarna krijgt u automatisch het tabblad “Power Query” op uw Excel-lint.

Als dit tabblad “POWER QUERY” op de een of andere manier niet verschijnt, hoeft u zich daar geen zorgen over te maken. U kunt het toevoegen met behulp van de COM Add-ins-optie.

• Ga naar het tabblad Bestand ➜ Opties ➜ Invoegtoepassingen.

• Selecteer in de “Add-In”-opties “COM Add-ins” en klik op GO.
• Vink daarna het vakje “Microsoft Power Query voor Excel” aan.

• Klik aan het einde op OK.

Excel 2016, 2019, Office 365

Als u Excel 2016 of Excel 2019 gebruikt of een OFFICE 365-abonnement hebt, staat dit al op het tabblad Gegevens , als een groep met de naam “GET & TRANSFORM” (ik vind die naam leuk, u ook?).

Excel Mac

Als u Excel op Mac gebruikt, ben ik bang dat hiervoor geen Power Query-invoegtoepassing bestaat en dat u alleen een bestaande query kunt vernieuwen , maar dat u geen nieuwe kunt maken of zelfs maar een query kunt bewerken ( LINK ).

Power Query-editor

Power Query heeft een eigen editor waar u de gegevens kunt ophalen, alle stappen kunt uitvoeren om query’s te maken en deze vervolgens in de spreadsheet kunt laden. Om de Power Query Editor te openen, gaat u naar het tabblad Gegevens en naar Ophalen en transformeren ➜ Gegevens ophalen ➜ Start Power Query Editor.

Hieronder ziet u een eerste voorbeeld van de editor die u krijgt als u deze opent.

Laten we nu elke sectie in detail verkennen:

1. Lint

Laten we eens kijken naar alle beschikbare tabbladen:

• Bestand: Vanaf het tabblad Bestand kunt u de gegevens laden, de editor verwijderen en de queryparameters openen.
• Home : Op het tabblad HOME heb je opties om geladen gegevens te beheren, zoals kolommen en rijen verwijderen en verplaatsen.
• Transformeren : dit tabblad bevat alle opties die u nodig hebt om gegevens te transformeren en op te schonen, zoals kolommen samenvoegen, transponeren, enz.
• Kolom toevoegen : Hier heeft u de mogelijkheid om nieuwe kolommen toe te voegen aan de gegevens die u in de geavanceerde editor heeft.
• Weergave : vanaf dit tabblad kunt u wijzigingen aanbrengen in de weergave Geavanceerde Query-editor en de geladen gegevens.

2. Stappen toegepast

Aan de rechterkant van de editor ziet u een deelvenster met query-instellingen dat de querynaam bevat en alle stappen die in een reeks worden toegepast.

Wanneer u met de rechtermuisknop op een stap klikt, krijgt u een lijst met opties die u kunt uitvoeren, zoals hernoemen, verwijderen, bewerken, omhoog of omlaag gaan, enz. en wanneer u op een stap klikt, brengt de editor u naar de transformatie die voor die stap is uitgevoerd.

Kijk hieronder waar je in totaal alle vijf de stappen hebt toegepast en als ik op de 4e stap klik, kom ik bij de transformatie in stap vier terecht waar de kolomnamen niet zijn veranderd.

3. Vragen

In het queryvenster aan de linkerkant worden alle query’s weergegeven die u momenteel in de werkmap heeft. Het is eigenlijk een plek waar u alle vragen kunt beheren.

Wanneer u met de rechtermuisknop op een querynaam klikt, ziet u alle opties die u kunt gebruiken (kopiëren, verwijderen, dupliceren, enz.)

U kunt ook een nieuwe query maken door met de rechtermuisknop op de lege ruimte in het queryvenster te klikken en vervolgens de optie voor gegevensbron te selecteren.

4. Formulebalk

Zoals ik al zei, genereert elke keer dat u een stap in de editor toepast, M-code voor die stap, en u kunt deze code zien in de formulebalk. U kunt eenvoudig op de formulebalk klikken om de code te bewerken.

Zodra u M-code leert gebruiken, kunt u ook een stap maken door de code te schrijven en eenvoudigweg op de knop “FX” te klikken om een aangepaste stap in te voeren.

5. Gegevensoverzicht

Het gegevensvoorbeeldgebied ziet eruit als een Excel-spreadsheet, maar wijkt enigszins af van een normaal spreadsheet waarin u rechtstreeks een cel of gegevens kunt bewerken. Wanneer u gegevens in de editor laadt (dat doen we zo meteen), worden alle kolommen met de kopteksten met de kolomnamen weergegeven en vervolgens de rijen met de gegevens.

Bovenaan elke kolom ziet u het gegevenstype van de gegevens in de kolom. Wanneer u gegevens in de editor laadt, past Advanced Query (bijna elke keer) automatisch het juiste gegevenstype toe op elke kolom.

U kunt op de knop linksboven in de kolomkop klikken om het gegevenstype dat op de kolom wordt toegepast te wijzigen. Het bevat een lijst met alle gegevenstypen waaruit u kunt putten.

En aan de linkerkant van de kolomkop vindt u de filterknop waarmee u de kolomwaarden kunt filteren. Opmerking: Wanneer u de waarden van een kolom filtert, beschouwt de geavanceerde query deze als één enkele stap en wordt deze vermeld in Toegepaste stappen.

Als u met de rechtermuisknop op de kolomkop klikt, ziet u dat er een menu is met een lijst met opties die u kunt gebruiken om de gegevens te transformeren en een van de opties en PQ te gebruiken die daarin worden opgeslagen als een stap in het toegepaste proces. niet.

Gegevensbronnen voor Power Query

Het beste van Power Query is dat u gegevens uit meerdere bronnen kunt ophalen, die gegevens kunt transformeren en vervolgens in de spreadsheet kunt laden. Wanneer u op Gegevens ophalen klikt in GET & TRANSFORM, ziet u de volledige lijst met gegevensbronnen die u in de editor kunt laden.

Laten we nu eens kijken naar enkele gegevensbronnen:

• Uit tabel/bereik : Met deze optie kunt u gegevens rechtstreeks vanuit het actieve werkblad in de Geavanceerde Query-editor laden.
• Uit werkmap : vanuit een andere werkmap die op uw computer staat. U hoeft dit bestand alleen maar te lokaliseren met behulp van een geopend dialoogvenster en het haalt automatisch de gegevens uit dit bestand op.
• Uit tekst/CSV : haal gegevens op uit een tekstbestand of een door komma’s gescheiden bestand, waarna u deze in de spreadsheet kunt laden.
• Uit map : het haalt alle bestanden uit de map en ondersteunt gegevens in een krachtige query-editor. (Zie dit: Excel-bestanden uit één map combineren).
• Van internet : Met deze optie haalt u gegevens op van een webadres. Stel u voor dat u een bestand hebt dat op internet is opgeslagen of dat u een webpagina heeft waarvan u de gegevens moet ophalen.

Gegevens laden in Power Query Editor

Laten we nu leren hoe we gegevens in de krachtige query-editor kunnen laden. Hier vindt u een lijst met namen van studenten en hun scores ( LINK ).

U laadt gegevens rechtstreeks vanuit de spreadsheet, dus u moet eerst het bestand openen en vervolgens de onderstaande stappen volgen:

• Pas eerst een Excel-tabel toe op de gegevens (zelfs als u dat niet doet, zal Excel dit voor u doen voordat de gegevens in de PQ-editor worden geladen).
• Selecteer nu een cel in de tabel en klik op “Uit tabel/bereik” (tabblad Gegevens ophalen en transformeren).

• Zodra u op de knop klikt, bevestigt Excel het gegevensbereik om er een Excel-tabel op toe te passen.

• Op dit moment hebt u de gegevens in de Power Query Editor en ziet deze er ongeveer zo uit als hieronder.

• Hier kun je zien:
  • In de formulebalk heeft PQ de M-code gegenereerd voor de tabel die u zojuist in de editor hebt geladen.
  • Aan de linkerkant van de editor bevindt zich het queryvenster met de lijst met query’s.
  • Aan de rechterkant, in de query-instellingen, vindt u de sectie met de naam ‘Toegepaste stappen’, waar u alle stappen vermeldt. Opmerking: u moet denken dat u geen “Gewijzigd type” hebt gedaan, maar er is een stap met de naam “Gewijzigd type”. Laat me u de INTELLIGENTIE van POWER QUERY vertellen wanneer u gegevens in de editor laadt. Deze controleert automatisch de juiste gegevenstypen voor alle kolommen en past deze toe.

Power Query-voorbeelden (tips en trucs)

U kunt leren hoe u enkele van de basistaken kunt uitvoeren die u normaal gesproken doet met functionele formules in Excel, maar met PowerQuery kunt u dit met slechts een paar klikken doen:

1. Vervang waarden

U heeft een lijst met waarden en u wilt een of meer waarden vervangen door iets anders. Met behulp van Power Query kunt u in een mum van tijd een query maken en deze waarden vervangen.

In de onderstaande lijst moet je mijn naam “Puneet” vervangen door “Punit”.

• Bewerk eerst de lijst in de Geavanceerde query-editor.
• Ga daarna in de Power Query Editor naar “Tabblad Transformeren” en klik op “Waarden vervangen”.

• Voer nu bij “Waarde te vinden” “Puneet” in en bij “Vervangen door” voer “Punit” in en klik daarna op OK.

• Zodra u op OK klikt, worden alle waarden vervangen door de nieuwe waarden en klikt u nu op ‘Sluiten en laden’ om de gegevens in de spreadsheet te laden.

2. Gegevens sorteren

Net als bij normaal sorteren kunt u gegevens sorteren met PowerQuery en ik gebruik dezelfde lijst met namen die u in het bovenstaande voorbeeld hebt gebruikt.

• Laad eerst de gegevens in de krachtige query-editor.
• Op het tabblad Start vindt u twee sorteerknoppen (Oplopend en Aflopend).
• Klik op een van deze knoppen om te sorteren.

3. Kolommen verwijderen

Stel dat u ergens gegevens heeft en daar enkele kolommen uit moet verwijderen. Het punt is dat je deze kolommen elke keer dat je nieuwe gegevens toevoegt, moet verwijderen, toch? Maar een powerquery kan daarvoor zorgen.

• Selecteer de kolom of meerdere kolommen die u wilt verwijderen.

• Klik nu met de rechtermuisknop en selecteer “Verwijderen”.

Snelle tip: Er is ook een optie om “Andere kolommen verwijderen” waar u alle niet-geselecteerde kolommen kunt verwijderen.

4. Kolommen splitsen

Net als de tekst-naar-kolom-optie, heb je “Kolom splitsen” in de powerquery. Laat me je vertellen hoe het werkt.

• Selecteer de kolom en ga naar het tabblad Start ➜ Transformeren ➜ Kolom splitsen ➜ Op scheidingsteken.

• Selecteer de aangepaste versie in de vervolgkeuzelijst en voer daarin “–” in.
• Nu heb je hier drie verschillende opties voor het splitsen van een kolom.
  • Meest linkse scheidingsteken
  • Meest rechtse scheidingsteken
  • Elke keer dat het scheidingsteken voorkomt

Als u slechts één scheidingsteken in een cel heeft, werken ze alle drie hetzelfde, maar als u meer dan één scheidingsteken heeft, moet u dienovereenkomstig kiezen.

5. Hernoem een kolom

U kunt de naam van een kolom eenvoudig wijzigen door met de rechtermuisknop te klikken en vervolgens op “Hernoemen” te klikken.

Snelle tip : stel dat u een vraag hebt om de naam van een kolom te wijzigen en dat iemand anders de naam per ongeluk heeft gewijzigd. U kunt deze naam met een enkele klik herstellen.

6. Dubbele kolom

In Power Query is er een eenvoudige optie om een dubbele kolom te maken. Klik eenvoudig met de rechtermuisknop op de kolom waarvoor u een dubbele kolom wilt maken en klik vervolgens op “Kolom dupliceren”.

7. Kolom of rij transponeren

Bij machtsvragen is omzetting kinderspel. Ja, slechts één klik.

• Nadat u de gegevens in de Geavanceerde Query-editor heeft geladen, hoeft u alleen maar de kolom(men) of rij(en) te selecteren.
• Ga naar het tabblad Transformeren ➜ Tabel ➜ Transponeren.

8. Vervang/verwijder fouten

Normaal gesproken kunt u om fouten in Excel te vervangen of te verwijderen de optie Zoeken en vervangen of VBA-code gebruiken. Maar in PowerQuery is het veel eenvoudiger. Kijk in de onderstaande kolom waar u fouten heeft en u kunt deze verwijderen en vervangen.

Wanneer u met de rechtermuisknop op de kolom klikt, heeft u beide opties.

• Vervang fouten
• Verwijder fouten

9. Wijzig het gegevenstype

U hebt gegevens in een kolom, maar deze hebben niet de juiste indeling. Dus elke keer dat u het formaat moet wijzigen.

• Bewerk eerst de gegevens in de krachtige query-editor.
• Selecteer daarna de kolom en ga naar het tabblad Transformeren.
• Selecteer nu bij Gegevenstype ‘Datum’ als type.

10. Voeg een kolom uit voorbeelden toe

In de geavanceerde query is er een optie om een voorbeeldkolom toe te voegen die niet daadwerkelijk een voorbeeld is dat verband houdt met de huidige kolom.

Laat me je een voorbeeld geven:

Stel je voor dat je dagnamen uit een datumkolom nodig hebt. In plaats van een formule of een andere optie te gebruiken, kunt u ‘Kolom uit voorbeelden toevoegen’ gebruiken.

Hier leest u hoe u het moet doen:

• Klik met de rechtermuisknop op een kolom en klik op ‘Kolom uit voorbeelden toevoegen’.

• Hier krijgt u een lege kolom. Klik op de eerste cel in de kolom om een lijst met waarden te krijgen die u kunt invoegen.

• Selecteer “Naam van de dag van de week vanaf datum” en klik op OK.

Boom! je nieuwe column is hier.

11. Wijzig dozen

U heeft de volgende opties voor het wijzigen van hoofdletters en kleine letters in PowerQuery.

• Klein
• Hoofdletters
• Schrijf elk woord met een hoofdletter

Dit kunt u doen door met de rechtermuisknop op een kolom te klikken en een van de drie bovenstaande opties te selecteren. Of ga naar het tabblad Transformeren ➜ Tekstkolom ➜ Opmaak.

12. Trimmen en schoonmaken

Om gegevens te wissen of ongewenste spaties te verwijderen , kunt u de TRIM- en CLEAN-opties in PowerQuery gebruiken. De stappen zijn eenvoudig:

• Klik met de rechtermuisknop op een kolom of selecteer alle kolommen als u meerdere kolommen heeft.
• Ga naar het tabblad Transformeren ➜ Tekstkolom ➜ Opmaak.
  1. TRIM: om witte ruimtes aan het einde en het begin van een cel te verwijderen.
  2. CLEAN: om niet-afdrukbare tekens uit een cel te verwijderen.

13. Voeg een voor-/achtervoegsel toe

U heeft dus een lijst met waarden en uit deze lijst wilt u in elke cel een voorvoegsel/achtervoegsel toevoegen. In Excel kun je de aaneenschakelingsmethode gebruiken, maar in PowerQuery is er voor beide een eenvoudig te gebruiken optie.

• Selecteer eerst de kolom waaraan u een voorvoegsel/achtervoegsel moet toevoegen.
• Ga vervolgens naar het tabblad Transformeren ➜ Tekstkolom ➜ Opmaak ➜ Voorvoegsel toevoegen/achtervoegsel toevoegen.

• Zodra u op een van de opties klikt, krijgt u een dialoogvenster waarin u tekst kunt invoeren.

• En nadat u de tekst hebt ingevoerd, klikt u op OK.

14. Waarden extraheren

Als je een formule-nerd bent, dan ben je er zeker van dat je het met me eens bent dat het extraheren van tekst of getallen uit een cel het combineren van verschillende functies vereist. Maar PowerQuery heeft veel van deze problemen opgelost. Je hebt zeven manieren om waarden uit een cel te extraheren.

15. Alleen de datum of tijd

Het komt vaak voor dat je de datum en tijd allebei in één cel hebt staan, maar dat je er één nodig hebt.

• Selecteer de kolom met de gecombineerde datum en tijd.
• Als je wil:
  • Datum : klik met de rechtermuisknop op ➜ Transformeren ➜ Alleen datum.
  • Tijd : klik met de rechtermuisknop op ➜ Transformeren ➜ Alleen tijd.

16. Combineer datum en tijd

Nu weet u hoe u datum en tijd kunt scheiden. Maar vervolgens moet je weten hoe je ze moet combineren.

• Selecteer eerst de datumkolom en klik op de optie “Alleen datum”.
• Selecteer daarna beide kolommen (Datum en Tijd) en ga naar het tabblad Transformeren en ga vanuit de groep “Datum en tijdkolom” naar Datum en klik op “Datum en tijd combineren”.

17. Afgeronde getallen

Hier zijn de volgende opties die u heeft voor het afronden van getallen.

• Naar beneden afronden: Om een getal naar beneden af te ronden.
• Rond: Om een getal af te ronden.
• Afronding: Je kunt kiezen hoe ver je mijn decimalen kunt afronden.

Hier zijn de stappen:

• Selecteer de kolom en klik met de rechtermuisknop op ➜ Transformeren ➜ Rond .
  1. Naar beneden afronden: Om een getal naar beneden af te ronden.
  2. Rond: Om een getal af te ronden.
  3. Afronding: Je kunt kiezen hoe ver je mijn decimalen kunt afronden.

Opmerking: Wanneer u de optie “#3 Round” selecteert, moet u het aantal decimalen invoeren dat u wilt afronden.

18. Berekeningen

Er zijn opties die u kunt gebruiken om berekeningen uit te voeren (veel). U vindt al deze opties op het tabblad Transformeren (in de groep Numerieke kolommen).

• Basis
• Statistieken
• Wetenschapper
• Trigonometrie
• Ronde
• Informatie

Om een van deze berekeningen uit te voeren, moet u de kolom en vervolgens de optie selecteren.

19. Groepeer op

Stel dat u een grote gegevensset heeft en een samenvattende tabel wilt maken. Dit is wat u moet doen:

• Klik op het tabblad Transformeren op de knop ‘Groeperen op’ en u krijgt een dialoogvenster.

• Selecteer nu vanuit dit dialoogvenster de kolom waarmee u wilt groeperen en voeg daarna een naam toe, selecteer de bewerking en de kolom waarin u waarden heeft.

• Klik aan het einde op OK.

Opmerking: Er zijn ook geavanceerde opties in de optie “Groeperen op” die u kunt gebruiken om een groepstabel met meerdere niveaus te maken.

20. Verwijder negatieve waarden

In een van mijn blogposts heb ik zeven methoden genoemd om negatieve signalen te verwijderen , en powerquery is daar een van. Klik met de rechtermuisknop op een kolom, ga naar de optie Transformeren en klik vervolgens op ‘Absolute waarde’.

Hierdoor worden onmiddellijk alle negatieve signalen uit de waarden verwijderd.

Gegevens in het spreadsheet laden

Zodra u uw gegevens heeft omgezet, kunt u deze in de spreadsheet laden en voor verdere analyse gebruiken. Op het tabblad Start bevindt zich een knop met de naam “Sluiten en laden”. Als u erop klikt, krijgt u een vervolgkeuzelijst met extra opties:

• Sluiten en laden
• Sluiten en inladen

• Zodra u op de knop klikt, worden de volgende opties weergegeven:

• Selecteer hoe u deze gegevens in uw spreadsheet wilt weergeven.
  • Schilderen
  • Draaitabelrapport:
  • Tafel draait
  • Maak slechts één verbinding
  • Waar wil je de gegevens plaatsen?
    • Bestaand werkblad
    • Nieuw werkblad.
  • Voeg deze gegevens toe aan het gegevensmodel.
• Selecteer gewoon de tabeloptie en het nieuwe werkblad, laat het gegevensmodel uitgeschakeld en klik op OK.

• Op het moment dat u op OK klikt, wordt een nieuw werkblad met de gegevens toegevoegd.

Meer voorbeelden om te leren

Een query automatisch vernieuwen

Van alle voorbeelden die ik hier heb aangehaald, is dit het belangrijkste. Wanneer u een query maakt, kunt u deze automatisch vernieuwen (u kunt een timer instellen).

En hier zijn de stappen:

• Klik op het tabblad Gegevens op “Query’s en verbindingen” en u krijgt het venster Query’s en verbindingen aan de rechterkant van het venster.

• Klik nu met de rechtermuisknop op de vraag en vink “Elke verversen” aan en voer de notulen in.

Een formule en functie gebruiken in Power Query

Net zoals u functies en formules in een Excel-spreadsheet kunt gebruiken, heeft Advanced Query een eigen lijst met functies die u kunt gebruiken. De basisprincipes van de functies en formules in PowerQuery zijn hetzelfde als de spreadsheetfuncties van Excel.

In PQ moet u een nieuwe aangepaste kolom toevoegen om een functie of formule toe te voegen.

Laten we een voorbeeld nemen: In de onderstaande gegevens (reeds in de PQ-editor) staat de voor- en achternaam ( DOWNLOAD LINK ) .

Stel je voor dat je de twee namen moet samenvoegen en een kolom voor de volledige naam moet maken. In dit geval kunt u een eenvoudige formule invoeren om de namen van de twee kolommen samen te voegen.

• Ga eerst naar het tabblad Kolom toevoegen en klik op “Aangepaste kolom”.

• Voer nu in het dialoogvenster voor de aangepaste kolom de naam in van de nieuwe kolom “Volledige naam” of hoe u de nieuwe kolom ook wilt noemen.

• • In de aangepaste kolomformule moet u de formule invoeren. Voer daarom de onderstaande formule in :

 [First Name]&" "&[Last Name] 

• Wanneer u een formule invoert in de “Aangepaste kolomformule”, controleert PQ de formule die u hebt ingevoerd en geeft een bericht weer “Er zijn geen syntaxisfouten gedetecteerd”. Als er een fout is, wordt er een berichtfout weergegeven op basis van het type fout.
• Zodra u de formule heeft ingevoerd en deze geen fouten bevat, drukt u gewoon op OK.
• Je hebt nu een nieuwe kolom aan het einde van de gegevens die waarden bevat uit twee kolommen (voornaam en achternaam).

Een functie gebruiken in Power Query

Op dezelfde manier kunt u ook een functie gebruiken terwijl u een aangepaste kolom toevoegt, en Power Query heeft een enorme lijst met functies die u kunt gebruiken.

Laten we begrijpen hoe we een functie kunnen gebruiken met een eenvoudig en eenvoudig voorbeeld. Ik ga verder met het bovenstaande voorbeeld, waarbij we een nieuwe kolom hebben toegevoegd waarin voornaam en achternaam worden gecombineerd.

Maar nu moet u de volledige naamtekst in deze kolom naar hoofdletters converteren. De functie die u kunt gebruiken is “ Text.Upper ” . Zoals de naam al doet vermoeden, converteert het tekst naar tekst in hoofdletters.

• Ga eerst naar het tabblad Kolom toevoegen en klik op de aangepaste kolom.

• • Voer nu in het dialoogvenster voor de aangepaste kolom de kolomnaam en de onderstaande formule in het formulevak voor de aangepaste kolom in:

 Text.Upper([Full Name]) 

• En wanneer u op OK klikt, wordt er een nieuwe kolom gemaakt met alle namen in hoofdletters.
• Het volgende is het verwijderen van de oude kolom en het hernoemen van de nieuwe kolom. Klik dus met de rechtermuisknop op de eerste kolom en selecteer verwijderen.

• Hernoem uiteindelijk de nieuwe kolom naar “Volledige naam”.

Er zijn in totaal 700 functies die u kunt gebruiken in Power Query terwijl u een nieuwe kolom toevoegt. Hier is de volledige lijst van Microsoft voor deze functies. Bekijk ze eens.

Hoe een query in PQ te wijzigen

Als u wijzigingen wilt aanbrengen in de query die al in uw werkmap staat, kunt u deze eenvoudig bewerken en vervolgens die wijzigingen aanbrengen. Op het tabblad Gegevens bevindt zich een knop met de naam Query’s en verbindingen.

Wanneer u op deze knop klikt, wordt aan de rechterkant een venster geopend met alle query’s die u in de huidige werkmap heeft.

U kunt met de rechtermuisknop op de querynaam klikken en Bewerken selecteren. Vervolgens krijgt u deze in de krachtige query-editor om deze te bewerken.

Wanneer u een query bewerkt, kunt u zien dat alle stappen die u eerder hebt uitgevoerd, worden vermeld in de “Toegepaste stappen”, die u ook kunt bewerken of nieuwe stappen kunt uitvoeren.

En als u klaar bent met uw bewerkingen, kunt u eenvoudig op de knop ‘Sluiten en laden’ klikken.

Export- en importverbindingen

Als u een verbinding heeft die u voor een zoekopdracht heeft gebruikt en u die verbinding nu met iemand anders wilt delen, kunt u die verbinding exporteren als een odc-bestand.

In de querytabel bevindt zich een knop met de naam “Verbinding exporteren” en als u erop klikt, kunt u de verbinding van die query in uw systeem opslaan.

En als u een verbinding wilt importeren die door iemand anders wordt gedeeld, kunt u eenvoudig naar het tabblad Gegevens gaan en in het venster Ophalen en transformeren op Bestaande verbindingen klikken .

En klik vervolgens op de knop “Bladeren naar meer” waar u het verbindingsbestand kunt vinden dat met u is gedeeld en het in uw werkmap kunt importeren.

PowerQuery-taal (M-code)

Zoals ik eerder al zei, genereert PowerQuery voor elke stap die u in PowerQuery zet een code (aan de backend) die M Code wordt genoemd. Op het tabblad Home bevindt zich een knop genaamd ” Geavanceerde editor” die u kunt gebruiken om de code te bekijken.

En wanneer u op de geavanceerde editor klikt, wordt de code-editor weergegeven en deze code ziet er als volgt uit:

M is een hoofdlettergevoelige taal en gebruikt net als alle andere talen variabelen en uitdrukkingen. De basisstructuur van de code ziet er als volgt uit, waar de code begint met de LET-expressie.

In deze code hebben we twee variabelen en de waarden die daarop zijn ingesteld. Om de waarde te verkrijgen werd uiteindelijk de IN-expressie gebruikt. Wanneer u nu op OK klikt, wordt in het resultaat de waarde geretourneerd die is toegewezen aan de variabele ‘Variabelenaam’.

Bekijk deze bron voor meer informatie over Power Query Language.

Aan het einde

U kunt het zich niet veroorloven om POWER QUERY te vermijden. Als je zo denkt, kunnen veel dingen die we doen met Excel-functies of VBA-codes worden geautomatiseerd, en ik ben er zeker van dat deze tutorial je zal inspireren om het steeds vaker te gebruiken.