Optimer Power Query ved udvidelse af tabelkolonner

Den enkelthed og brugervenlighed, der gør det muligt for Power BI-brugere hurtigt at indsamle data og generere interessante og effektive rapporter, så de kan træffe intelligente forretningsbeslutninger, giver også brugerne mulighed for nemt at generere forespørgsler med dårlig ydeevne. Dette sker ofte, når der er to tabeller, der er relateret på den måde, en fremmed nøgle relaterer SQL-tabeller eller SharePoint-lister på. (For posten er dette problem ikke specifikt for SQL eller SharePoint og forekommer i mange backend-dataudtrækningsscenarier, især hvor skemaet er flydende og kan tilpasses). Der er heller ikke noget i sagens natur galt med at gemme data i separate tabeller, der deler en fælles nøgle – faktisk er dette et grundlæggende element i databasedesign og normalisering. Men det betyder en bedre måde at udvide relationen på.

Se følgende eksempel på en SharePoint-kundeliste.

Og følgende placeringsliste, som den refererer til.

Når du opretter forbindelse til listen, vises placeringen som en post.

Disse data på øverste niveau indsamles via et enkelt HTTP-kald til SharePoint-API'en (ignorerer metadatakaldet), som du kan se i et hvilket som helst webfejlfindingsprogram.

Når du udvider posten, kan du se de felter, der er joinforbundet fra den sekundære tabel.

Når du udvider relaterede rækker fra én tabel til en anden, er Power BI's standardfunktionsmåde at generere et kald til Table.ExpandTableColumn. Du kan se dette i det genererede formelfelt. Denne metode genererer desværre et individuelt kald til den anden tabel for hver række i den første tabel.

Dette øger antallet af HTTP-kald med ét for hver række på den primære liste. Dette ser muligvis ikke ud som meget i ovenstående eksempel på fem eller seks rækker, men i produktionssystemer, hvor SharePoint-lister når hundredtusindvis af rækker, kan dette medføre en betydelig forringelse af oplevelsen.

Når forespørgsler når denne flaskehals, er den bedste afhjælpning at undgå funktionsmåden for kald pr. række ved hjælp af en klassisk tabeljoinforbindelse. Dette sikrer, at der kun er ét kald til at hente den anden tabel, og resten af udvidelsen kan forekomme i hukommelsen ved hjælp af den fælles nøgle mellem de to tabeller. Forskellen i ydeevne kan i nogle tilfælde være enorm.

Start først med den oprindelige tabel, og notér den kolonne, du vil udvide, og sørg for, at du har id'et for elementet, så du kan matche det. Den fremmede nøgle navngives typisk på samme måde som det viste navn på kolonnen, hvor Id er tilføjet. I dette eksempel er det LocationId.

For det andet skal du indlæse den sekundære tabel og sørge for at inkludere id'et, som er den fremmede nøgle. Højreklik på panelet Forespørgsler for at oprette en ny forespørgsel.

Til sidst skal du joinforbinde de to tabeller ved hjælp af de respektive kolonnenavne, der stemmer overens. Du kan typisk finde dette felt ved først at udvide kolonnen og derefter søge efter de tilsvarende kolonner i eksemplet.

I dette eksempel kan du se, at LocationId på den primære liste stemmer overens med Id'et på den sekundære liste. Brugergrænsefladen omdøber dette til Location.Id for at gøre kolonnenavnet entydigt. Lad os nu bruge disse oplysninger til at flette tabellerne.

Når du højreklikker på forespørgselspanelet og vælger Ny forespørgsel Kombiner>>flet forespørgsler som ny, får du vist en brugervenlig brugergrænseflade, der kan hjælpe dig med at kombinere disse to forespørgsler.

Vælg hver tabel på rullelisten for at få vist et eksempel på forespørgslen.

Når du har valgt begge tabeller, skal du vælge den kolonne, der joinforbinder tabellerne logisk (i dette eksempel er det LocationId fra den primære tabel og Id fra den sekundære tabel). I dialogboksen får du besked om, hvor mange af rækkerne der matcher ved hjælp af den fremmede nøgle. Du vil sandsynligvis bruge standardjointypen (venstre ydre) til denne type data.

Vælg OK , så får du vist en ny forespørgsel, som er resultatet af joinforbindelsen. Udvidelse af posten nu betyder ikke yderligere kald til backend.

Opdatering af disse data resulterer kun i to kald til SharePoint – ét for den primære liste og ét for den sekundære liste. Joinforbindelsen udføres i hukommelsen, hvilket reducerer antallet af kald til SharePoint markant.

Denne fremgangsmåde kan bruges til to tabeller i PowerQuery, der har en tilsvarende fremmed nøgle.

Bemærk

SharePoint-brugerlister og taksonomi er også tilgængelige som tabeller og kan joinforbindes på præcis den måde, der er beskrevet ovenfor, forudsat at brugeren har de nødvendige rettigheder til at få adgang til disse lister.