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 aussagekräftige Berichte zu erstellen, um intelligente Geschäftsentscheidungen zu treffen, ermöglicht es den Benutzern auch, leicht schlecht funktionierende Abfragen zu erstellen. Dies ist häufig der Fall, wenn es zwei Tabellen gibt, die durch einen Fremdschlüssel mit SQL-Tabellen oder SharePoint-Listen verbunden sind. (Dieses Problem ist übrigens nicht spezifisch für SQL oder SharePoint, sondern tritt in vielen Backend-Datenextraktionsszenarien auf, insbesondere wenn das Schema fließend und anpassbar ist) Es spricht auch nichts dagegen, Daten in separaten Tabellen zu speichern, die einen gemeinsamen Schlüssel haben - dies ist sogar ein Grundprinzip des Datenbankdesigns und der Normalisierung. Aber es gibt eine bessere Möglichkeit, die Beziehung zu erweitern.

Betrachten Sie das folgende Beispiel einer SharePoint-Kundenliste.

Und die folgende Ortsliste, auf die sie sich bezieht.

Bei der ersten Verbindung mit der Liste wird der Ort als Eintrag angezeigt.

Diese Top-Level-Daten werden durch einen einzigen HTTP-Aufruf an die SharePoint-API (ohne den Metadaten-Aufruf) erfasst, den Sie in jedem Web-Debugger sehen können.

Wenn Sie den Datensatz erweitern, sehen Sie die aus der Sekundärtabelle verbundenen Felder.

Wenn Sie zusammenhängende Zeilen aus einer Tabelle in eine andere erweitern, generiert Power BI standardmäßig einen Aufruf von Table.ExpandTableColumn. Sie können dies in dem generierten Formelfeld sehen. Leider erzeugt diese Methode für jede Zeile der ersten Tabelle einen eigenen Aufruf der zweiten Tabelle.

Dadurch erhöht sich die Anzahl der HTTP-Aufrufe um einen für jede Zeile in der Primärliste. Im obigen Beispiel mit fünf oder sechs Zeilen mag dies nicht viel erscheinen, aber in Produktionssystemen, in denen SharePoint-Listen Hunderttausende von Zeilen umfassen, kann dies zu einer erheblichen Beeinträchtigung der Benutzerfreundlichkeit führen.

Wenn Abfragen diesen Engpass erreichen, besteht die beste Abhilfe darin, das Call-per-Row-Verhalten zu vermeiden, indem eine klassische Tabellenverknüpfung verwendet wird. Dadurch wird sichergestellt, dass es nur einen Aufruf zum Abrufen der zweiten Tabelle gibt und der Rest der Expansion im Speicher unter Verwendung des gemeinsamen Schlüssels zwischen den beiden Tabellen erfolgen kann. Der Leistungsunterschied kann in manchen Fällen enorm sein.

Beginnen Sie mit der Originaltabelle, notieren Sie sich die Spalte, die Sie erweitern möchten, und stellen Sie sicher, dass Sie die ID des Elements haben, damit Sie es zuordnen können. Normalerweise wird der Fremdschlüssel ähnlich wie der Anzeigename der Spalte benannt, wobei Id angehängt wird. In diesem Beispiel ist es LocationId.

Zweitens laden Sie die sekundäre Tabelle, wobei Sie darauf achten, dass Sie die Ideinbeziehen, 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 unter Verwendung der entsprechenden Spaltennamen, die übereinstimmen. Sie können dieses Feld in der Regel finden, indem Sie zunächst die Spalte erweitern und dann in der Vorschau nach den passenden Spalten suchen.

In diesem Beispiel können Sie sehen, dass LocationId in der Primärliste mit Id in der Sekundärliste übereinstimmt. Die Benutzeroberfläche benennt diese 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 neuezusammenführen wählen, sehen Sie eine freundliche Benutzeroberfläche, die Ihnen hilft, diese beiden Abfragen zu kombinieren.

Wählen Sie jede Tabelle aus der Dropdown-Liste aus, um eine Vorschau der Abfrage anzuzeigen.

Nachdem Sie beide Tabellen ausgewählt haben, wählen Sie die Spalte aus, die die Tabellen logisch miteinander verbindet (in diesem Beispiel ist es LocationId aus der primären Tabelle und Id aus der sekundären Tabelle). Das Dialogfeld zeigt Ihnen an, wie viele der Zeilen mit diesem Fremdschlüssel übereinstimmen. Für diese Art von Daten werden Sie wahrscheinlich die Standard-Join-Art (left outer) verwenden wollen.

Wählen Sie OK und Sie sehen eine neue Abfrage, die das Ergebnis der Verknüpfung ist. Das Erweitern des Datensatzes bedeutet jetzt keine zusätzlichen Aufrufe an das Backend.

Die Aktualisierung dieser Daten führt zu nur zwei Aufrufen an SharePoint - einem für die Primärliste und einem für die Sekundärliste. Die Verknüpfung wird im Arbeitsspeicher durchgeführt, wodurch die Anzahl der Aufrufe an SharePoint erheblich reduziert wird.

Dieser Ansatz kann für zwei beliebige Tabellen in PowerQuery verwendet werden, die einen passenden Fremdschlüssel haben.

Hinweis

SharePoint-Benutzerlisten und -Taxonomien sind ebenfalls als Tabellen zugänglich und können auf genau die oben beschriebene Weise verbunden werden, vorausgesetzt, der Benutzer verfügt über die entsprechenden Berechtigungen für den Zugriff auf diese Listen.