Aanbevolen procedures voor Power Query-taalstroom

In deze zelfstudie leert u over de Power Query-taalstroom en enkele van de best practices. U leert ook hoe u uw gegevens eenvoudig kunt transformeren om de best mogelijke resultaten te krijgen. Gegevenstransformaties zullen uw rapport optimaliseren en er aantrekkelijk uit laten zien.

Inhoudsopgave

• Stel gegevenstypen in zonder Power Query-taalstroom
  • Stel gegevenstypen in met behulp van het tabblad Transformeren
  • Stel gegevenstypen in met behulp van de kolomkop
  • Stel gegevenstypen in met behulp van kolompictogrammen
• Gegevenstypen instellen met Power Query Language Flow
  • Verwijder onnodige kolommen
  • Wijs geschikte gegevenstypen toe aan kolommen en beperk rijen
  • Consolideer redundante stappen in de Power Query-taalstroom
• Organiseer query's voor een betere Power Query-taalstroom
• Samenvatting van de Power Query-taalstroom
• Conclusie

Stel gegevenstypen in zonder Power Query-taalstroom

De absolute minimumtransformaties die u altijd moet uitvoeren, zijn het hernoemen en instellen van gegevenstypen voor uw kolommen.

Gegevenstypen worden gebruikt om waarden in M ​​te classificeren . Een pictogram naast de kolomnaam toont het toegewezen gegevenstype voor een veld. In deze voorbeeldtabel ziet u een ABC123-pictogram naast de kolomnamen. Dat pictogram vertegenwoordigt het gegevenstype Elk, wat aangeeft dat er geen gegevenstype aan die kolom is toegewezen. 

Uit de boeken is automatische typedetectie ingeschakeld. Dit genereert automatisch stappen voor gewijzigd type in uw query's. Zelfs als dat is ingeschakeld, moet u er altijd voor zorgen dat Power Query de gegevenstypen correct raadt voor elk van de kolommen in al uw tabellen.

Volg deze stappen om die instelling in of uit te schakelen.

Onder de optie Globaal kunt u een van de drie opties voor typedetectie selecteren die voor u beschikbaar zijn.

Onder de optie Huidig ​​bestand kunt u de opties voor typedetectie schakelen, afhankelijk van uw algemene instelling.

Stel gegevenstypen in met behulp van het tabblad Transformeren

De gebruikersinterface biedt meerdere manieren om gegevenstypen voor uw kolommen in te stellen. In het gedeelte Elke kolom van het tabblad Transformeren vindt u de knop Gegevenstype detecteren .

Als u alle kolommen selecteert en op die knop klikt, worden automatisch de gegevenstypen van alle kolommen ingesteld. Omdat alleen de bovenste 200 rijen worden gescand, moet u echter nog steeds valideren of Power Query het juiste gegevenstype heeft.

Als u een kolom uit uw tabel selecteert, kunt u het gegevenstype zien in de sectie Elke kolom.

Als u op de vervolgkeuzeknop klikt en Tekst selecteert, verandert het pictogram in de geselecteerde kolom van ABC123 in alleen ABC .

U ziet dan een stap Gewijzigd type in het deelvenster Toegepaste stappen.

Op het tabblad Home vindt u ook de optie om het gegevenstype in te stellen.

Stel gegevenstypen in met behulp van de kolomkop

Een andere manier is om met de rechtermuisknop op een koptekst te klikken en Type wijzigen te selecteren . Van daaruit kunt u het juiste gegevenstype voor uw kolommen kiezen.

Stel gegevenstypen in met behulp van kolompictogrammen

De meest gebruikelijke manier om gegevenstypen in te stellen, is door op het pictogram naast de kolomnaam te klikken. Het toont u alle beschikbare gegevenstypen.

Stel in de voorbeeldtabel het gegevenstype van de kolom OrderDate in op Date .

Afhankelijk van het gegevenstype van de kolom die u selecteert, biedt Power Query een reeks transformaties en opties die van toepassing zijn op dat specifieke gegevenstype op het tabblad Transformeren , het tabblad Kolom toevoegen en de sectie Slim filter .

Dus als u een datumkolom selecteert en naar het tabblad Transformeren gaat, ziet u opties onder de knop Datum.

Als u echter een tekstkolom selecteert, zijn de opties onder de knop Datum niet meer beschikbaar.

Voor slim filteren hebben tekstkolommen tekstfilters en datumkolommen datumfilters.

Wijzig voor de volgende kolom de kolomnaam in Klantsleutel en het gegevenstype in Geheel getal .

Wijzig vervolgens het gegevenstype van de kolommen Kanaal, Valutacode en Magazijncode in Tekst.

Wijzig daarna de naam van de Delivery Region Index in Delivery Region Key en het gegevenstype in Whole Number . Doe vervolgens hetzelfde voor de kolommen Productbeschrijvingsindex en Bestelhoeveelheid .

Ten slotte stelt u voor de kolommen Eenheidsprijs, Regeltotaal en Eenheidsprijs het gegevenstype in op Vast decimaal getal .

Gegevenstypen instellen met Power Query Language Flow

Vanwege de wijzigingen in de kolommen zijn er veel stappen gemaakt in het deelvenster Toegepaste stappen. Het probleem is dat door meerdere keren hetzelfde type transformaties uit te voeren, de voorbeeldtabelquery inefficiënt wordt. Dit is iets dat u moet vermijden.

Om uw query efficiënt te maken, probeert u een enkele stap te maken door die specifieke transformatie toe te passen op al uw kolommen voordat u een nieuwe stap maakt.

Dupliceer de voorbeeldtabelquery en hernoem deze Best Practice . Klik in het deelvenster Toegepaste stappen met de rechtermuisknop op de eerste transformatie die in de vorige query is uitgevoerd en selecteer Verwijderen tot einde . Bevestig vervolgens de verwijdering van de stap in het dialoogvenster Stap verwijderen .

Daarnaast zijn hier enkele best practices. Het eerste is het maken van parameters om de locatie van de gegevensbron te bevatten. Dit maakt het gemakkelijker om problemen op te lossen wanneer een bestandsnaam is gewijzigd.

Als u parameters wilt maken, klikt u op Parameters beheren op het tabblad Home en selecteert u Nieuwe parameter .

Een andere manier is om met de rechtermuisknop op het Query-venster te klikken en Nieuwe parameter te selecteren .

Daarna verschijnt een dialoogvenster Parameters beheren . Noem de parameter FileLocation en stel Type in op Tekst. Voor de voorgestelde waarden stelt u deze in op Lijst met waarden , zodat u uw tekenreeks kunt plakken en meerdere locaties kunt wijzigen of toevoegen waartussen u kunt wisselen.

Ga vervolgens naar uw Verkenner en selecteer uw bestand. Kopieer het pad en plak de parameters erin. Als u klaar bent, drukt u op OK .

Ga terug naar de Best Practice-query en klik op de stap Bron in het deelvenster Toegepaste stappen. Wijzig vervolgens het hardgecodeerde bestandspad in de formulebalk met FileLocation .

Verwijder onnodige kolommen

Verwijder alle onnodige kolommen om ruimte te besparen en de prestaties te verbeteren. Neem alleen de gegevens mee die u nodig hebt, want het toevoegen van tabellen en kolommen is veel eenvoudiger dan het verwijderen ervan.

De gemakkelijkste manier om dat te doen is via de stap Kolommen kiezen op het tabblad Start. Als u op die knop klikt, verschijnt er een dialoogvenster waarin u de kolommen kunt kiezen die u wilt behouden. U moet uw tabellen ontwerpen en vormgeven met een specifiek doel, zodat ze het beste passen bij de analyse die u gaat uitvoeren.

Van daaruit deselecteer je de kolommen die je niet nodig hebt in je tabel. Voor deze voorbeeldtabel is de kardinaliteit van de kolom OrderNumber hoog. Het is het beste om die kolom te deselecteren, omdat dit van invloed is op de bestandsgrootte en de algehele prestaties.

Als u geen locatiegegevens nodig heeft voor de analyse van de voorbeeldquery, kunt u ook de optie Leveringsregio-index het beste deselecteren . Ten slotte, aangezien de tabel al een eenheidsprijs en hoeveelheid heeft, is de kolom Regeltotaal niet nodig.

Druk na het deselecteren van kolommen op OK . Als u de kolomselectie wilt wijzigen, wist u gewoon het tandwielpictogram naast de stapnaam in het deelvenster Toegepaste stappen.

Wijs geschikte gegevenstypen toe aan kolommen en beperk rijen

Wijs vervolgens gegevenstypen toe voor alle kolommen. Selecteer alle kolommen en klik op Gegevenstype detecteren op het tabblad Transformeren.

Gegevenstype detecteren detecteert automatisch de gegevenstypen van kolommen op basis van de scan van de bovenste 200 rijen. Controleer dus en zorg ervoor dat Power Query de juiste gegevenstypen instelt.

Het is best practice om het aantal rijen te beperken. Begint uw boekjaar op 1 juli, dan kunt u de gegevens van juni weglaten of een parameter instellen.

Er zit echter een addertje onder het gras als u een parameterwaarde in de LuckyTemplates-service wilt kunnen wijzigen . Nadat u uw rapport hebt gepubliceerd, moeten uw parameterwaarden van het type Tekst of Decimaal zijn.

Om daarmee om te gaan, maakt u een filter op de gegevens door op de vervolgkeuzeknop in de kolom OrderDate te klikken. Klik vervolgens op Datumfilters en selecteer Na .

Wijzig in het dialoogvenster de eerste parameter in is na of gelijk aan en voer vervolgens de datum in. In dit voorbeeld was de ingevoerde datum 1 juli 2014.

Nadat u op OK hebt gedrukt , wordt het filter toegepast in uw tabel. Maak vervolgens een parameter en noem deze DatesFrom . Stel Type in op Tekst en voer de datum in de parameter Huidige waarde in.

Ga nu terug naar de Best Practice-query en vervang de intrinsieke #date door Date.From(DatesFrom) .

Als u op het vinkje klikt zonder de functie Date.From , wordt een fout geretourneerd. Dat komt omdat DatesFrom een ​​teksttype is, terwijl het veld een datumtype heeft. De functie Date.From converteert tekst naar datum.

Hernoem alle kolommen die niet worden verborgen in het gegevensmodel. De naam moet beknopt, zelfbeschrijvend en gebruiksvriendelijk zijn. Houd er rekening mee dat u een gegevensmodel ontwerpt voor mensen die uw rapport gaan gebruiken.

Consolideer redundante stappen in de Power Query-taalstroom

Het volgende dat u moet doen, is overbodige stappen consolideren (zoals het hernoemen, verwijderen en wijzigen van kolomgegevenstypen). Bovendien zijn stappen als het herschikken van kolommen dingen waar u op moet letten bij het zoeken naar overtolligheden.

De tabellen die u in het gegevensmodel laadt, worden nooit in uw rapport weergegeven. Dat maakt de kolomvolgorde irrelevant.

Een andere best practice is om uw stappen een andere naam te geven in het deelvenster Toegepaste stappen. De namen van de stappen beschrijven zichzelf en worden gebruikt als variabelen in de M- code.

Namen die spaties of speciale tekens bevatten, worden geschreven met de notatie tussen aanhalingstekens. Het betekent dat de namen tussen dubbele aanhalingstekens staan ​​en dat er een hekje of hekje voor staat, waardoor de M- code moeilijk te lezen is. U kunt de spaties weglaten of er een onderstrepingsteken tussen plaatsen.

Het toevoegen van aanvullende documentatiedetails door opmerkingen in het venster Geavanceerde editor te plaatsen, is ook een best practice in Power Query. U kunt dit ook doen in de Step Property Description . Ze worden weergegeven als tooltip-annotaties wanneer u de muisaanwijzer op een stap plaatst met een uitroepteken in het deelvenster Toegepaste stappen.

Weten waarom u een bepaalde keuze hebt gemaakt tijdens de initiële ontwikkeling, is uiterst nuttig wanneer u een bestand na enige tijd opnieuw moet bezoeken. Om documentatiedetails toe te voegen, klikt u met de rechtermuisknop op een stap in het deelvenster Toegepaste stappen en selecteert u Eigenschappen .

Er verschijnt een dialoogvenster Stapeigenschappen waarin u de reden voor filteren of transformeren kunt schrijven.

Organiseer query's voor een betere Power Query-taalstroom

Een van de meest gebruikelijke best practices in Power Query is het organiseren van uw query's . Maak mappen voor parameters, functies, stagingquery's en query's die in het gegevensmodel worden geladen. Selecteer in dit voorbeeld de query's FileLocation en DatesFrom en klik er met de rechtermuisknop op. Selecteer vervolgens Verplaatsen naar groep en klik op Nieuwe groep .

Voeg vervolgens een naam toe aan de geselecteerde zoekopdrachten en druk op OK .

Nadat u uw query's hebt gegroepeerd, ziet uw queryvenster er als volgt uit.

Zorg ervoor dat u voor al uw staging-query's het laden uitschakelt door Laden inschakelen uit te schakelen.

Een ander ding om in deze tutorial te bespreken, is de taalstroom. Elk van de stappen in het deelvenster Toegepaste stappen transformeert een waarde die u kunt zien wanneer u erop klikt.

De eerste gegevens kwamen en begonnen in de navigatiestap en er werden kolommen geselecteerd. Vervolgens werden gegevenstypen gewijzigd en werd een datumbereik ingesteld. Kolommen werden ook hernoemd.

Alle stappen retourneren een tabeltypewaarde. Als u het venster Geavanceerde editor opent, ziet u een let- expressie en een in- clausule. Daartussenin staat een lijst met namen van stappen of variabelen waaraan uitdrukkingen zijn toegewezen.

De query retourneert alles wat volgt op de in- clausule die verwijst naar de laatste stap in uw lijst met variabelen. De M- engine volgt dan de afhankelijkheidsketen terug vanaf de in- clausule om al het onnodige te elimineren en om transformaties indien mogelijk terug te duwen naar de bron.

Samenvatting van de Power Query-taalstroom

Als u naar de formule kijkt, ziet u de stroom van de stappen die in de query zijn uitgevoerd. U ziet bij elke stap ook de functies die door de gebruikersinterface worden gebruikt .

De eerste stap die in de query is gemaakt, was de kolomselectie. Toen de stap werd uitgevoerd met behulp van de gebruikersinterface, werd de functie Table.SelectColumns aangeroepen. Als eerste parameter nam het een tabel die verwees naar de variabelenaam van de vorige stap. Vervolgens werden alle geselecteerde kolomnamen vermeld.

De tweede stap transformeerde de kolomtypen door de functie Table.TransformColumnTypes aan te roepen . De eerste parameter wordt de uitvoer van de vorige stap genoemd. Vervolgens vermeldde het een reeks transformatielijsten.

De derde stap stelt een datumbereikfilter in met behulp van de functie Table.SelectRows . Er was een query van het type tabel nodig als eerste argument. In dit voorbeeld werd verwezen naar de uitvoer van de stap Type wijziging.

De laatste stap hernoemde de kolommen met behulp van de functie Table.RenameColumns . De uitvoer van de vorige stap werd gebruikt als het eerste argument. Vervolgens vermeldde het een reeks hernoemingslijsten.

Alle functies die via de gebruikersinterface worden toegepast, beginnen met het woord Tabel . Ze namen allemaal een tabelwaarde als eerste parameter en transformeerden die waarde vervolgens.

Hoewel de code sequentieel lijkt omdat elke stap verwijst naar de vorige stap, is de volgorde niet vereist. Als u een stap verplaatst, wordt de query nog steeds uitgevoerd omdat de M- engine altijd de afhankelijkheidsketen volgt.

Het hebben van de gebruikersinterface is handig, maar het gaat er altijd van uit dat u de resultaten van de vorige transformatie wilt transformeren. In de meeste gevallen zal dat waarschijnlijk waar zijn en als dat niet het geval is, moet u die waarde waarnaar wordt verwezen in de code handmatig bijwerken.

Power Query-gegevenstypen en -connectoren
LuckyTemplates-gegevenssets: typen en naamconventies
Best practices voor het laden en transformeren van gegevens

Conclusie

Gegevenstransformatie is nodig om uw gegevens gegroepeerd en georganiseerd te houden. Het maakt gegevensontwikkeling sneller omdat u eenvoudig problemen in de Power Query-taalstroom kunt opsporen en wijzigingen in uw rapport kunt aanpassen.

Melissa