Was ist eigentlich der Unterschied zwischen Duplizieren und Verweis? Und wofür verwendet man einen Verweis? Und warum sollte man Abfragen deaktivieren?

Excel Power Pivot oder Power BI

Sowohl Power BI als auch Excel Power Pivot nutzen Power Query. Die folgenden Tipps und Tricks gelten für beide Werkzeuge. Unterschiede erläutern wir an der jeweiligen Stelle.

Verweise & Duplikate

Legen Sie in Power Query eine neue Abfrage an. Im Editor sehen Ihre Daten in einer Vorschau. Dort in der linken Hälfte finden sind Ihre neue und gegebenenfalls andere Abfragen in einer Liste dargestellt.

Power Query Editor

Sie können jede Abfrage duplizieren oder einen Verweis darauf erstellen.

Duplizieren und Verweis

Die Funktion Duplizieren kopiert die Abfrage. Die kopierte Abfrage ist damit unabhängig vom Original. Für einen Test sehr nützlich.

Erstellen Sie hingegen einen Verweis, wird eine zweite Abfrage angelegt. Diese neue Abfrage greift auf das Ergebnis der ersten zu. Die M-Syntax der zweiten Abfrage ist denkbar einfach.

M-Syntax eines Verweis

Wozu benutzt man Verweise?

Daten liegt häufig in einer großen zweidimensionalen Form vor. Beispielsweise bei einer Tabelle Auftragspositionen.

Zweidimensionale Tabelle mit Autragsdaten

Dabei wären uns eine eigene Materialtabelle, eine Auftragspositionstabelle und eine Kundentabelle lieber. Mit diesen Tabellen (und sicherlich noch einer Kalender-Tabelle) können wir ein Star-Schema aufbauen. In diesen separierten Tabellen dienen die Felder Materialnr und Kundennr als Verknüpfung.

Leider kann man oftmals an der Datenquelle nichts ändern. Trotzdem können wir uns mit Power Query behelfen. Als erstes nehmen wir uns die die Materialtabelle vor. Folgende Schritte führen wir nacheinander aus:

  • Verweise auf die Auftragspositionen-Tabelle
  • umbenennen der neuen Abfrage in DimMaterial
  • Funktion Spalten auswählen und nur die Spalten Materialnr und Materialtext behalten.
  • Spalte Materialnr markieren und Funktion Zeilen entfernen – Duplikate entfernen auswählen

Fertig ist die Dimensionstabelle DimMaterial.

DimMaterial als Ergebnis der Transformation

Und Für die Kundendaten gehen Sie gleich vor. Damit bleibt am Ende folgende Tabelle übrig:

DimKunden als Ergebnis der Transformation

Im letzten Schritt bearbeiten wir die Faktentabelle. Die Felder Materialtext oder Kundenort benötigen wir dort nicht mehr. Diese Spalten dürfen wir in der Ausgangstabelle aber nicht löschen! Die Abfragen DimMaterial und DimKunden verweisen auf diese Spalten. Daher erstellen wir einen weiteren Verweis auf die Tabelle Auftragspositionen und nennen diese FaktAuftragspositionen. Über die Funktion Spalten wählen aktivieren wir noch folgende Spalten:

FaktMaterial als Ergebnis der Transformation

Diese drei Tabellen können wir später im Datenmodell über die Felder Kundennr und Materialnr wieder in Beziehung setzen.

Abfragen nicht laden

Vielleicht haben Sie im Kontextmenü einer Abfrage in Power BI die Option Laden aktivieren gefunden. Deaktivieren Sie diese Option, wird die Abfrage kursiv angezeigt. Damit wird die Abfrage nicht mehr geladen und die Ergebnisse der Abfrage stehen im Bericht nicht mehr zur Verfügung.

Laden deaktivieren in PowerBI

In Excel Power Pivot ist das ein kleines bisschen umständlicher. Im Dialog Laden In definieren Sie, ob das Ergebnis der Abfrage in ein Tabellenblatt, in das Datenmodell oder in beide geladen werden soll. Hier wählen Sie Nur Verbindung erstellen.

Laden deaktivieren in Power Pivot

Anders als in Power BI  sehen Sie in Power Query die Abfrage nicht kursiv dargestellt. In Excel sehen Sie das im Fenster Abfragen und Verbindungen.

Laden deaktiviert in Excel

Wozu benötigen wir deaktivierte Abfragen? Im obigen Beispiel Fall haben wir vier Tabellen:

  • Auftragspositionen
  • DimMaterial
  • DimKunden
  • FaktAuftragspositionen

Die Tabelle Auftragspositionen dient uns nur als Quelle für die anderen Abfragen. Das Ergebnis dieser Abfrage benötigen wir nicht im Datenmodell. Durch Nur Verbindung erstellen erreichen wir genau das: Die Tabelle wird nicht geladen.

Organisation mit Gruppen

Mit den gezeigten Methoden erstellen Sie in Ihren Projekten mehr Abfragen. Einige davon werden nicht geladen. Andere sind Verweise. Damit Sie die Übersicht nicht verlieren, können Sie Abfragen entsprechend benennen. Ich benenne Abfragen meist mit einem Unterstrich als Präfix, wenn sie nicht geladen werden. Also beispielsweise \Auftragspositionen_.

Darüber hinaus können Sie Abfragen zusätzlich in Ordnern organisieren. In Power Query Gruppen genannt. Im der Liste der Abfragen finden Sie die Funktion Neue Gruppe. Legen Sie eine Gruppe an wird automatisch eine weitere mit Namen Andere Abfragen angelegt. In dieser anderen Gruppe legt Power Query neue, nicht zugeordnete Abfragen an. Via Drag and Drop können Sie die Abfragen organisieren:

Gruppen in Power Query

In Excel in Abfragen und Verbindungen wird diese Struktur entsprechend dargestellt:

Gruppen in Abfragen und Verbindungen in Excel