Lab 01 : Power Query gebruiken in Excel

  • 30 minuten

Nu kunt u Power Query proberen met deze begeleide, stapsgewijze use-case. Gebruik de opgegeven voorbeeldgegevensbronbestanden om de oefeningen te voltooien.

Lab 01 - Analyse in Excel: Power Query gebruiken in Excel

De geschatte tijd voor het voltooien van dit lab is 30 minuten.

In dit praktijkleerlab voert u de volgende taken uit:

  1. Gebruik Power Query om verbinding te maken met een .csv brongegevensbestand - Klanten
  2. Gebruik Power Query transformaties om een kolom te splitsen op scheidingsteken - Klanten
  3. Gebruik Power Query om verbinding te maken met een Excel-brongegevensbestand - Offertes
  4. Gebruik Power Query-transformaties om een draaitabel op te heffen - Offertes
  5. Gebruik Power Query-transformaties om op te schonen - Offertes

Labvereisten

De volgende vereisten en instellingen moeten zijn ingesteld om de oefeningen met succes te voltooien:

  • U moet verbinding hebben met internet.

  • Microsoft Office moet zijn geïnstalleerd.

  • Meld u aan voor Microsoft Power BI.

  • U moet minimaal een computer hebben met twee kernen en 4 GB RAM-geheugen waarop Windows 8 of hoger of Windows Server 2008 R2 of hoger wordt uitgevoerd.

  • U kunt Microsoft Edge of Google Chrome gebruiken.

  • Controleer of u een 32-bits of 64-bits besturingssysteem hebt om te bepalen of u de 32-bits of 64-bits toepassingen moet installeren.

    Notitie

    64-bits Excel en Power BI Desktop is het beste.

    Belangrijk

    Download de inhoud van de leerling/student: maak een map met de naam ANALYST-LABS op station C: van uw lokale computer. Download en extraheer alle inhoud uit https://aka.ms/modern-analytics-labs naar de map ANALYST-LABS die u hebt gemaakt (C:\ANALYST-LABS).

  • Download en installeer Power BI Desktop met behulp van een van de volgende opties:

    • Als u Windows 10 of hoger hebt, gebruikt u Microsoft App Store om Power BI Desktop toepassing te downloaden en te installeren.
    • Download en installeer Microsoft Power BI Desktop.

  • Als u Power BI Desktop al hebt geïnstalleerd, controleert u of u de nieuwste versie van Power BI hebt gedownload.

Documentstructuur

Brongegevens of beginbestanden voor elk lab bevinden zich in elke labmap.

  • U voltooit Lab 01 met behulp van Power Query in de Excel-toepassing.

  • U voltooit Lab 02A en Lab 02B met behulp van Power BI Desktop toepassing.

  • U voltooit Lab 03A met behulp van Power BI Desktop-, Power BI-service- en Excel-toepassingen.

  • U voltooit Lab 03B met behulp van Excel en Power BI-service-toepassingen.

Elk lab wordt geleverd met stapsgewijze instructies die moeten worden gevolgd en bevat schermafbeeldingen in de instructies. De belangrijkste acties voor elke stap worden aangeduid met vetgedrukte tekst. Let op notities, tips en andere belangrijke informatie. Elk lab bevat een voltooid oplossingsbestand dat u als referentie kunt gebruiken.

Overzicht

De geschatte tijd voor het voltooien van dit lab is 30 minuten. In dit lab voert u de volgende taken uit:

  1. Power Query gebruiken om verbinding te maken met een CSV-brongegevensbestand - Klanten

  2. Gebruik Power Query transformaties om kolommen te splitsen op scheidingsteken - Klanten

  3. Gebruik Power Query om verbinding te maken met een XLSX-brongegevensbestand - Offertes

  4. Gebruik Power Query-transformaties om een draaitabel op te heffen - Offertes

  5. Gebruik Power Query-transformaties om op te schonen - Offertes

    Notitie

    Dit lab is gemaakt op basis van de verkoopactiviteiten van het fictieve Wi-Fi bedrijf genaamd SureWi, dat is geleverd door P3 Adaptive. De gegevens zijn eigendom van P3 Adaptive en zijn gedeeld met het doel om excel- en Power BI-functionaliteit te demonstreren met voorbeeldgegevens uit de branche. Elk gebruik van deze gegevens moet deze toeschrijving aan P3 Adaptive omvatten.

Oefening 1: Power Query gebruiken om verbinding te maken met CSV - Customers.csv

In deze oefening gebruikt u Excel om verbinding te maken met een CSV-brongegevensbestand.

Taak 1: Excel starten

In deze taak start u een nieuw, leeg werkblad om aan de slag te gaan.

  1. Start Excel.

    Schermopname van het Excel-logo.

  2. Maak een nieuwe, lege werkmap.

    Schermafbeelding van het selectiescherm Nieuwe lege werkmap in Excel.

Taak 2: Power Query gebruiken om verbinding te maken met CSV

In deze taak maakt u verbinding met het CSV-brongegevensbestand klanten.

  1. Selecteer het tabblad Gegevens op het hoofdlint van Excel.

  2. Selecteer Gegevens > ophalen uit bestand > uit tekst/CSV.

    Schermopname van de menuoptie Gegevens > uit tekst/CSV.

  3. Ga naar het bestand C:\ANALYST-LABS\Lab 01\MAIAD Lab 01 - Gegevensbron - Customers.csv .

    In het gebied Preview wordt een voorbeeld van de gegevens, kolomnamen en waarden van de klant weergegeven.

    Notitie

    Dit voorbeeld is alleen een voorbeeld van de gegevens.

    Schermopname van het navigatorvenster met voorbeeldgegevens uit Lab 01 - Gegevensbron - Customers.csv.

  4. Selecteer de knop Gegevens transformeren . Hiermee wordt het venster Power Query-editor geopend.

    Notitie

    Wanneer u in Power Query werkt, kunt u het beste het Power Query-editor venster maximaliseren, zodat u een volledig overzicht krijgt van de menu's, deelvensters en opties van het Power Query venster.

  5. Standaard wordt het deelvenster Query's aan de linkerkant van het Power Query-editor venster samengevouwen. Selecteer de pijl in het deelvenster Query's om het deelvenster Query's uit te vouwen en te openen.

    Schermopname van het venster Power Query-editor met het deelvenster Query's samengevouwen en de pijl om het deelvenster uit te vouwen.

  6. Klik in het deelvenster Query's met de rechtermuisknop op de standaardquerynaam met de naam MAIAD Lab 01 - Gegevensbron - Klanten en wijzig de naam van de query in Klanten.

    Schermopname van het geopende deelvenster Query's, met Lab 01 - Gegevensbron - Klantenquery en de knop Naam wijzigen gemarkeerd.

    Tip

    Query's die u gebruikt als onderdeel van een gegevensmodel, moeten een duidelijke, beschrijvende, gebruiksvriendelijke naam krijgen die beschrijft wat de gegevens vertegenwoordigen. Bijvoorbeeld Klanten, Offertes, Facturen, Producten, Geografie, enzovoort.

Oefening 2: Gebruik Power Query transformaties om kolommen te splitsen op scheidingsteken - Klanten

In deze oefening gebruikt u Power Query om de voornaam uit de kolom Contactpersoon te extraheren.

Taak 1: Kolom uit voorbeeld gebruiken

In deze taak maakt u een nieuwe kolom met de naam Voornaam door de transformatie Kolomkolom > toevoegen uit voorbeelden te gebruiken om de contactpersoon te splitsen op basis van een scheidingsteken.

  1. Selecteer in het raster Voorbeeld de kolom Contact .

  2. Selecteer op het tabblad Kolom toevoegen de pijl-omlaag Kolom uit voorbeelden en selecteer vervolgens de optie Van selectie .

    Schermopname van Power Query-editor venster met de menuopties Kolom toevoegen.

    Notitie

    Hiermee opent u een nieuw gebruikersinterfacevenster met de naam 'Kolommen toevoegen uit voorbeelden'. Dit venster ziet eruit als het Power Query voorbeeldraster, maar het is een apart venster waarin u de voorgestelde waarde kunt typen, zodat Power Query het patroon en de formule kunt identificeren die moeten worden toegepast om de eindresultaten te bereiken.

  3. Typ in het venster Kolom toevoegen uit voorbeelden in de kolom met de naam [Kolom1] de waarde 'Hugo' en voer vervolgens in.

    Schermopname van het venster Kolom toevoegen uit voorbeelden met Hugo ingevoerd in Kolom1.

    Notitie

    Nadat u op Enter drukt, identificeert Power Query of er een patroon in de gegevens bestaat om de waarden voor alle rijen te vullen.

  4. Dubbelklik in de standaardkop met de naam 'Tekst vóór scheidingsteken' en wijzig de naam van de nieuwe kolom in 'Voornaam'. Selecteer de knop OK.

    Schermopname van het venster Kolom uit voorbeelden met de naam van de standaardkolomnaam in Voornaam.

    Notitie

    In het Power Query-editor voorbeeldraster ziet u nu de kolom NEW met de naam Voornaam, die is gemaakt door de [Voornaam] uit de [Contactpersoon] te parseren met behulp van de kolom uit voorbeeldtransformatie.

    Schermopname van het raster Voorbeeld in Power Query-editor met de nieuwe kolom Voornaam.

Oefening 3: Power Query gebruiken om verbinding te maken met XLSX - Quotes.xlsx

In deze oefening gebruikt u Excel om verbinding te maken met een XLSX-brongegevensbestand.

Taak 1: Verbinding maken met XLSX-brongegevens vanuit het Power Query-editor-venster

In deze taak begint u vanuit het Power Query-editor venster.

  1. Selecteer in het menu Start Power Query de optie Nieuw Excel-werkmapbestand>.

    Schermopname van het Power Query-editor venster met de opties op het tabblad Start met de optie Nieuwe excel-werkmap bronbestand >> geselecteerd.

  2. Navigeer naar het bestand C:\ANALYST-LABS\Lab 01\Data Source - Quotes.xlsx.

  3. Selecteer in het venster Navigator het werkblad met de naam Lab 01 - Quotes.

    Notitie

    Dit is alleen een voorbeeld van de gegevens.

    Schermopname van het venster Navigator met het werkblad Lab 01A - Aanhalingstekens gemarkeerd.

  4. Selecteer de knop OK om te laden als een tweede query in het venster Power Query-editor.

  5. Klik in het deelvenster Query's met de rechtermuisknop op de standaardquerynaam met de naam 'Lab 01 - Quotes' om de naam van de query te wijzigen in 'Quotes'.

    Schermafbeelding van het deelvenster Query's in het venster Power Query-editor, met de naam Lab 01 - Aanhalingstekens geselecteerd voor het wijzigen van de naam.

Oefening 4: Gebruik Power Query om draaitabel op te heffen - Offertes

In deze oefening gebruikt u Power Query transformaties om de gegevens over quotes voor Power Pivot te structureren.

Taak 1: De eerste rij gebruiken als transformatieknop Kopteksten

In deze taak verplaatst u de eerste rij met de kolomkopwaarden naar de tabelkoptekst.

Selecteer in het menu Start de knop Eerste rij als koptekst gebruiken .

Schermopname van het tabblad Start met de knop Eerste rij als koptekst gebruiken geselecteerd.

Taak 2: de menuoptie Draaitabeltransformatie opheffen gebruiken

In deze taak schakelt u de draaitabel van de gegevens voor aanhalingstekens op.

  1. Klik in het voorbeeldvenster met de rechtermuisknop op de kolom CustID om menuopties weer te geven.

  2. Kies vervolgens de optie Draaitabel andere kolommen opheffen .

    Schermopname van de kolom CustID en de optie Andere kolommen opheffen gemarkeerd voor selectie.

  3. Dubbelklik op de kolom Kenmerk en wijzig de naam van de kolom QuoteDate.

  4. Dubbelklik op de kolom Waarde en wijzig de naam van de kolom QuoteAmt.

    Voor

    Schermopname van de kolomkoppen CustID, Kenmerk en Waarden voordat de naam ervan wordt gewijzigd.

    Na

    Schermopname van de kolommen Kenmerk en Waarde waarvan de naam is gewijzigd in QuoteDate en QuoteAmt.

Oefening 5: Gebruik Power Query om op te schonen - Offertes

In deze oefening gebruikt u Power Query transformaties om de gegevens over offertes op te schonen.

Taak 1: de transformatie Vervangen gebruiken

In deze taak gebruikt u een vervangingstechniek om de QuoteDate te wijzigen in een volledige datum die u kunt converteren naar een gegevenstype Datum.

  1. Klik in het venster Voorbeeld met de rechtermuisknop op de kolom QuoteDate om menuopties weer te geven.

  2. Kies vervolgens de optie Waarden vervangen... .

    Schermopname van de kolom QuoteDate met de menuoptie Waarden vervangen gemarkeerd.

  3. In het venster Waarden vervangen van de gebruikersinterface:

    1. Voer een afbreekstreepje - in het tekstvak Te zoeken waarde in.

    2. Voer /1/ in het tekstvak Vervangen door in.

    Schermopname van het venster Waarden vervangen met vermeldingen in de vakken Te zoeken waarde en Vervangen door.

    1. Selecteer de knop OK.

Taak 2: het pictogram Gegevenstype gebruiken

In deze taak gebruikt u het pictogram Gegevenstype om het gegevenstype te wijzigen van Tekst in Datum.

  1. Selecteer het abc-pictogram in de kolomkop QuoteDate dat aangeeft dat de kolom een gegevenstype Tekst is.

  2. Kies de optie Datum in het menuopties voor gegevenstypen.

    Schermopname van het geselecteerde pictogram voor de gegevensindeling QuoteDate.

Taak 3: Het gegevensmodel sluiten en laden

In deze taak laadt u de tabellen Klanten en Offertes in het gegevensmodel.

  1. Selecteer in het menu Startde optie Sluiten & Laden > Sluiten & Laden naar... .

    Schermopname van de knop Sluiten & Laden geselecteerd en de optie Sluiten & Laden naar gemarkeerd.

  2. Selecteer in het venster Importeren het keuzerondje Alleen verbinding maken .

    Schermopname van het venster Gegevens importeren met de knop Alleen verbinding maken en het selectievakje Deze gegevens toevoegen aan het gegevensmodel ingeschakeld.

  3. Schakel het selectievakje naast Deze gegevens toevoegen aan het gegevensmodel in.

  4. Selecteer de knop OK.

    Notitie

    De geladen tabellen worden weergegeven in het venster Query's & Connections deelvenster met het totale aantal geladen rijen.

    Schermopname van het deelvenster Query's & Connections met de query Klanten met 7.560 rijen geladen en de query Quotes met 84.307 rijen geladen.

    Notitie

    Op dit moment hebben we verbinding gemaakt met de gegevensbronnen met behulp van Power Query en de selectievakjeoptie Deze gegevens toevoegen aan het gegevensmodel ingeschakeld. We hebben echter niet gezien waar deze gegevens zijn geladen. In Lab 02A gebruiken we Power BI Desktop om de Power Query-verbindingen, de tabel Klant en de tabel Offerte te importeren om het gegevensmodel te maken.

    Notitie

    Als u de query's wilt bewerken en het venster Power Query-editor opnieuw wilt starten, selecteert uGegevensquery's> & Connections om het deelvenster Query's & Connections weer te geven.

    Schermopname van de knop Query's & Connections onder het tabblad Gegevens.

    Notitie

    Wanneer u Sluiten en Laden naar... selecteert en de opties Gegevens importeren voor de eerste keer instelt, is dit de standaardinstelling. U kunt deze instellingen altijd bewerken door met de rechtermuisknop op de query te klikken in het deelvenster Query's & Connections en vervolgens de optie Laden naar... te selecteren om de instellingen voor Gegevens importeren weer te geven en bij te werken.

    Schermopname van laden naar... in het contextdialoogvenster voor een item in het deelvenster Query's & Connections.

Taak 4: Het bestand opslaan

In deze taak slaat u het Excel-bestand op met de queryverbindingen Klanten en Offertes .

  1. Selecteer bestand > opslaan op het hoofdlint van Excel.

  2. Ga naar de map C:\ANALYST-LABS\Lab 01 en sla het bestand op als Lab 01 - Mijn Solution.xlsx.

Samenvatting

In dit lab hebt u Power Query in Excel gebruikt om verbinding te maken met CSV- en XLSX-brongegevensbestanden, een nieuwe kolom gemaakt met Kolom uit voorbeeld, niet-draaitabele en toegepaste transformaties in Power Query, brongegevens in een gegevensmodel geladen en het Excel-bestand met de gegevensverbindingen opgeslagen.

Schermopname van de uiteindelijke resultaten in Excel met de query's Klanten en Offertes.