Optimera Power Query när du expanderar tabellkolumner

Enkelheten och användarvänligheten som gör det möjligt för Power BI-användare att snabbt samla in data och generera intressanta och kraftfulla rapporter för att fatta intelligenta affärsbeslut gör det också möjligt för användare att enkelt generera frågor som fungerar dåligt. Detta inträffar ofta när det finns två tabeller som är relaterade på det sätt som en sekundärnyckel relaterar SQL-tabeller eller SharePoint-listor. (För posten är det här problemet inte specifikt för SQL eller SharePoint och inträffar i många scenarier för extrahering av serverdelsdata, särskilt där schemat är flytande och anpassningsbart.) Det är heller inget fel med att lagra data i separata tabeller som delar en gemensam nyckel– det här är i själva verket en grundläggande grundsats för databasdesign och normalisering. Men det innebär ett bättre sätt att utöka relationen.

Tänk dig följande exempel på en SharePoint-kundlista.

Och följande platslista refererar den till.

När du först ansluter till listan visas platsen som en post.

Dessa data på den översta nivån samlas in via ett enda HTTP-anrop till SharePoint-API:et (ignorerar metadataanropet), som du kan se i alla webbfelsökare.

När du expanderar posten visas fälten som är kopplade från den sekundära tabellen.

När du expanderar relaterade rader från en tabell till en annan är standardbeteendet för Power BI att generera ett anrop till Table.ExpandTableColumn. Du kan se detta i det genererade formelfältet. Tyvärr genererar den här metoden ett enskilt anrop till den andra tabellen för varje rad i den första tabellen.

Detta ökar antalet HTTP-anrop med ett för varje rad i den primära listan. Detta kanske inte verkar så mycket i exemplet ovan på fem eller sex rader, men i produktionssystem där SharePoint-listor når hundratusentals rader kan detta orsaka en betydande försämring av upplevelsen.

När frågor når den här flaskhalsen är den bästa lösningen att undvika beteendet anrop per rad med hjälp av en klassisk tabellkoppling. Detta säkerställer att det bara finns ett anrop för att hämta den andra tabellen, och resten av expansionen kan ske i minnet med hjälp av den gemensamma nyckeln mellan de två tabellerna. Prestandaskillnaden kan vara enorm i vissa fall.

Börja först med den ursprungliga tabellen, notera den kolumn som du vill expandera och se till att du har objektets ID så att du kan matcha det. Vanligtvis namnges sekundärnyckeln ungefär som visningsnamnet för kolumnen med ID tillagt . I det här exemplet är det LocationId.

För det andra läser du in den sekundära tabellen och ser till att inkludera ID:t, som är sekundärnyckeln. Högerklicka på panelen Frågor för att skapa en ny fråga.

Anslut slutligen de två tabellerna med respektive kolumnnamn som matchar. Du kan vanligtvis hitta det här fältet genom att först expandera kolumnen och sedan leta efter matchande kolumner i förhandsversionen.

I det här exemplet kan du se att LocationId i den primära listan matchar ID i den sekundära listan. Användargränssnittet byter namn på detta till Location.Id för att göra kolumnnamnet unikt. Nu ska vi använda den här informationen för att sammanfoga tabellerna.

Genom att högerklicka på frågepanelen och välja Kombinera nya frågor>>som nya frågor visas ett användarvänligt användargränssnitt som hjälper dig att kombinera dessa två frågor.

Välj varje tabell i listrutan för att se en förhandsversion av frågan.

När du har valt båda tabellerna väljer du den kolumn som kopplar tabellerna logiskt (i det här exemplet är det LocationId från den primära tabellen och ID från den sekundära tabellen). Dialogrutan instruerar dig hur många av raderna som matchar med hjälp av den främmande nyckeln. Du vill förmodligen använda standardkopplingstypen (vänster yttre) för den här typen av data.

Välj OK så visas en ny fråga, vilket är resultatet av kopplingen. Att expandera posten nu innebär inte ytterligare anrop till serverdelen.

Uppdatering av dessa data resulterar bara i två anrop till SharePoint – ett för den primära listan och ett för den sekundära listan. Kopplingen utförs i minnet, vilket avsevärt minskar antalet anrop till SharePoint.

Den här metoden kan användas för två tabeller i PowerQuery som har en matchande sekundärnyckel.

Kommentar

SharePoint-användarlistor och taxonomi är också tillgängliga som tabeller och kan kopplas på exakt det sätt som beskrivs ovan, förutsatt att användaren har tillräcklig behörighet att komma åt dessa listor.