Power Query optimaliseren bij het uitbreiden van tabelkolommen
Dankzij de eenvoud en het gebruiksgemak waarmee Power BI snel gegevens kunnen verzamelen en interessante en krachtige rapporten kunnen genereren om intelligente zakelijke beslissingen te nemen, kunnen gebruikers ook eenvoudig slecht presterende query's genereren. Dit gebeurt vaak wanneer er twee tabellen zijn die gerelateerd zijn op de manier waarop een SQL tabellen of SharePoint gerelateerd. (Dit probleem is niet specifiek voor SQL of SharePoint en treedt op in veel scenario's voor gegevensextractie van back-enden, met name wanneer het schema vloeiend en aanpasbaar is.) Er is ook niets inherent mis met het opslaan van gegevens in afzonderlijke tabellen die een gemeenschappelijke sleutel delen. Dit is in feite een fundamenteel grondbeginsel van — databaseontwerp en -normalisatie. Maar het impliceert wel een betere manier om de relatie uit te breiden.
Kijk eens naar het volgende voorbeeld van SharePoint lijst met klanten.

En de volgende locatielijst waar deze naar verwijst.

Wanneer u voor het eerst verbinding maakt met de lijst, wordt de locatie weergegeven als een record.

Deze gegevens op het hoogste niveau worden verzameld via één HTTP-aanroep naar de SharePoint-API (waarbij de aanroep van metagegevens wordt genegeerd), die u in elk webdebugger kunt zien.

Wanneer u de record uitvoedt, ziet u de velden die zijn samengevoegd vanuit de secundaire tabel.

Bij het uitbreiden van gerelateerde rijen van de ene tabel naar de andere, is het standaardgedrag van Power BI het genereren van een aanroep naar Table.ExpandTableColumn . U kunt dit zien in het gegenereerde formuleveld. Helaas genereert deze methode een afzonderlijke aanroep naar de tweede tabel voor elke rij in de eerste tabel.

Hierdoor wordt het aantal HTTP-aanroepen met één verhoogd voor elke rij in de primaire lijst. Dit lijkt misschien niet veel in het bovenstaande voorbeeld van vijf of zes rijen, maar in productiesystemen waar SharePoint-lijsten honderdduizenden rijen bereiken, kan dit leiden tot een aanzienlijke verslechtering van de ervaring.
Wanneer query's dit knelpunt bereiken, kunt u het gedrag van aanroepen per rij het beste vermijden door gebruik te maken van een klassieke tabel-join. Dit zorgt ervoor dat er slechts één aanroep is om de tweede tabel op te halen en dat de rest van de uitbreiding in het geheugen kan plaatsvinden met behulp van de gemeenschappelijke sleutel tussen de twee tabellen. Het prestatieverschil kan in sommige gevallen enorm zijn.
Begin eerst met de oorspronkelijke tabel, noteert u de kolom die u wilt uitbreiden en zorgt u ervoor dat u de id van het item hebt, zodat u deze kunt vinden. De naam van de vreemde sleutel is doorgaans vergelijkbaar met de weergavenaam van de kolom met Id toegevoegd. In dit voorbeeld is dit LocationId.

Laad ten tweede de secundaire tabel, waarbij u ervoor zorgt dat de id wordt vermeld. Dit is de vreemde sleutel. Klik met de rechtermuisknop op het deelvenster Query's om een nieuwe query te maken.

Voeg ten slotte de twee tabellen samen met behulp van de respectieve kolomnamen die overeenkomen. U kunt dit veld doorgaans vinden door eerst de kolom uit te breiden en vervolgens te zoeken naar de overeenkomende kolommen in de preview.

In dit voorbeeld ziet u dat LocationId in de primaire lijst overeenkomt met Id in de secundaire lijst. In de gebruikersinterface wordt de naam Location.Id om de kolomnaam uniek te maken. Nu gaan we deze informatie gebruiken om de tabellen samen te voegen.
Door met de rechtermuisknop op het queryvenster te klikken en Nieuwe query combineren Query's combineren als nieuw te selecteren, ziet u een gebruikersinterface die u helpt deze twee query's > > te combineren.

Selecteer elke tabel in de vervolgkeuzelijst om een voorbeeld van de query te bekijken.

Nadat u beide tabellen hebt geselecteerd, selecteert u de kolom waarin de tabellen logisch worden samengezet (in dit voorbeeld is dit LocationId uit de primaire tabel en id uit de secundaire tabel). In het dialoogvenster ziet u hoeveel rijen overeenkomen met behulp van die vreemde sleutel. Waarschijnlijk wilt u het standaardtype voor joins (left outer) gebruiken voor dit soort gegevens.

Selecteer OK en u ziet een nieuwe query. Dit is het resultaat van de join. Het uitbreiden van de record betekent nu geen extra aanroepen naar de back-end.

Het vernieuwen van deze gegevens resulteert in slechts twee aanroepen naar SharePoint één voor de primaire — lijst en één voor de secundaire lijst. De join wordt uitgevoerd in het geheugen, waardoor het aantal aanroepen naar SharePoint.
Deze aanpak kan worden gebruikt voor twee tabellen in PowerQuery die een overeenkomende vreemde sleutel hebben.
Notitie
SharePoint en taxonomie zijn ook toegankelijk als tabellen en kunnen worden samengevoegd op exact dezelfde manier als hierboven wordt beschreven, mits de gebruiker voldoende bevoegdheden heeft om toegang te krijgen tot deze lijsten.