Optimieren von Power Query beim Erweitern von Tabellenspalten

Die Einfachheit und Benutzerfreundlichkeit, die es Power BI-Benutzern ermöglicht, schnell Daten zu sammeln und interessante und leistungsstarke Berichte zu generieren, um intelligente Geschäftsentscheidungen zu treffen, ermöglicht es Benutzern auch, leicht schlecht ausgeführte Abfragen zu generieren. Dies tritt häufig auf, wenn es zwei Tabellen gibt, die in der Weise miteinander verknüpft sind, wie ein Fremdschlüssel SQL-Tabellen oder SharePoint-Listen verknüpft. (Zur Information, dieses Problem ist nicht spezifisch für SQL oder SharePoint und tritt in vielen Back-End-Datenextraktionsszenarien auf, insbesondere wenn das Schema fließend und anpassbar ist.) Es spricht auch nichts grundsätzlich dagegen, Daten in separaten Tabellen zu speichern, die einen gemeinsamen Schlüssel besitzen – tatsächlich handelt es sich hierbei um einen grundlegenden Grundsatz des Datenbankentwurfs und der Normalisierung. Aber es impliziert eine bessere Möglichkeit, die Beziehung zu erweitern.

Betrachten Sie das folgende Beispiel einer SharePoint-Kundenliste.

Und die folgende Speicherortliste bezieht sich darauf.

Beim ersten Herstellen einer Verbindung mit der Liste wird der Speicherort als Datensatz angezeigt.

Diese Daten auf oberster Ebene werden über einen einzelnen HTTP-Aufruf der SharePoint-API (ignoriert den Metadatenaufruf) gesammelt, den Sie in einem beliebigen Webdebugger sehen können.

Wenn Sie den Datensatz erweitern, werden die Felder aus der sekundären Tabelle verknüpft.

Beim Erweitern verwandter Zeilen von einer Tabelle auf eine andere, besteht das Standardverhalten von Power BI darin, einen Aufruf zu Table.ExpandTableColumngenerieren. Dies wird im generierten Formelfeld angezeigt. Leider generiert diese Methode einen einzelnen Aufruf der zweiten Tabelle für jede Zeile in der ersten Tabelle.

Dadurch wird die Anzahl der HTTP-Aufrufe für jede Zeile in der primären Liste um eins erhöht. Dies mag im obigen Beispiel von fünf oder sechs Zeilen nicht so viel aussehen, aber in Produktionssystemen, in denen SharePoint-Listen Hunderte von Tausenden von Zeilen erreichen, kann dies zu einer erheblichen Beeinträchtigung der Erfahrung führen.

Wenn Abfragen diesen Engpass erreichen, besteht die beste Abhilfe darin, zeilenweise Aufrufe durch eine klassische Tabellenverknüpfung zu vermeiden. Dadurch wird sichergestellt, dass nur ein Aufruf zum Abrufen der zweiten Tabelle vorhanden ist, und der Rest der Erweiterung kann im Arbeitsspeicher auftreten, indem der gemeinsame Schlüssel zwischen den beiden Tabellen verwendet wird. Der Leistungsunterschied kann in einigen Fällen massiv sein.

Beginnen Sie zunächst mit der ursprünglichen Tabelle, notieren Sie die Spalte, die Sie erweitern möchten, und stellen Sie sicher, dass Sie über die ID des Elements verfügen, damit sie übereinstimmen können. Normalerweise wird der Fremdschlüssel ähnlich dem Anzeigenamen der Spalte mit angefügter ID benannt. In diesem Beispiel handelt es sich um "LocationId".

Laden Sie zweitens die sekundäre Tabelle, und stellen Sie sicher, dass die ID enthalten ist, die der Fremdschlüssel ist. Klicken Sie mit der rechten Maustaste auf den Bereich "Abfragen", um eine neue Abfrage zu erstellen.

Verknüpfen Sie schließlich die beiden Tabellen mit den entsprechenden Spaltennamen, die übereinstimmen. Sie können dieses Feld in der Regel finden, indem Sie zuerst die Spalte erweitern und dann in der Vorschau nach den übereinstimmenden Spalten suchen.

In diesem Beispiel können Sie sehen, dass "LocationId " in der primären Liste mit der ID in der sekundären Liste übereinstimmt. Die Benutzeroberfläche benennt dies in Location.Id um, um den Spaltennamen eindeutig zu machen. Verwenden wir nun diese Informationen, um die Tabellen zusammenzuführen.

Wenn Sie mit der rechten Maustaste auf den Abfragebereich klicken und Neue Abfrage>Kombinieren>Abfragen als Neu zusammenführen auswählen, wird eine benutzerfreundliche Benutzeroberfläche angezeigt, die Ihnen bei der Kombination dieser beiden Abfragen hilft.

Wählen Sie jede Tabelle aus der Dropdownliste aus, um eine Vorschau der Abfrage anzuzeigen.

Nachdem Sie beide Tabellen ausgewählt haben, wählen Sie die Spalte aus, die die Tabellen logisch verknüpft (in diesem Beispiel ist " LocationId " aus der Primärtabelle und der ID aus der sekundären Tabelle). Das Dialogfeld weist Sie an, wie viele Zeilen mit diesem Fremdschlüssel übereinstimmen. Sie möchten wahrscheinlich die Standardmäßige Verknüpfungsart (links außen) für diese Art von Daten verwenden.

Wählen Sie "OK" aus, und Es wird eine neue Abfrage angezeigt, die das Ergebnis der Verknüpfung ist. Durch das Erweitern des Datensatzes werden jetzt keine zusätzlichen Aufrufe an das Back-End impliziert.

Das Aktualisieren dieser Daten führt nur zu zwei Aufrufen von SharePoint – einer für die primäre Liste und eine für die sekundäre Liste. Die Verknüpfung wird im Arbeitsspeicher ausgeführt, wodurch die Anzahl der Aufrufe an SharePoint erheblich reduziert wird.

Dieser Ansatz kann für alle zwei Tabellen in PowerQuery verwendet werden, die über einen übereinstimmenden Fremdschlüssel verfügen.

Hinweis

SharePoint-Benutzerlisten und Taxonomie sind auch als Tabellen zugänglich und können genau wie oben beschrieben verknüpft werden, vorausgesetzt, der Benutzer verfügt über ausreichende Berechtigungen für den Zugriff auf diese Listen.