Lookups sind eine häufige Aufgabe bei der Datenaufbereitung in Power BI mit Power Query - beispielsweise um Mappings umzusetzen oder um Attribute an eine bestehende (Dimensions-)Tabelle anzuhängen. Dazu stehen in Power Query zumindest zwei Umsetzungsvarianten zur Verfügung: Merge Queries und Custom Columns.

In diesem Blogbeitrag werden diese beiden Umsetzungsvarianten gezeigt und gegenüber gestellt.

Ausgangssituation

Ausgangsbasis ist die Query Dim Countries mit 249 Datensätzen, die um die URL für die Längerflagge angereichert werden soll:

Diese URL ist in der zweiten Query Flags (206 Datensätze) zu finden:

Die Spalte ImageURL soll im Sinne der dimensionalen Modellierung mit einem Lookup an die Tabelle Dim Countries angehängt werden:

Hinweis: diese Report Page wird auf diejenigen Länder gefiltert, die einen Match in der Tabelle Flags haben (= 156 Datensätze).

Lösungsvariante 1: Lookup mittels Merge Query

Die naheliegende - und vermutlich weit verbreitete - Umsetzungsvariante für den Lookup ist eine Merge Query, die im Menü im Bereich Home -> Combine zu finden ist:

Im Merge Dialog wird definiert, welche Spalte aus der Query Dim Countries mit welcher Spalte aus der Query Flags verknüpft wird und welcher Join Typ angewendet werden soll. Für den Lookup ist in aller Regel der Left Outer Join die richtige Wahl, da die Inhalte der Lookup Spalte an die bestehende Query angehängt werden sollen, ohne diese zu filtern. Beachte bitte, daß von den 249 Datensätzen in der Query Dim Countries nur 156 Datensätze einen Treffer in der Query Flags haben ... weiters verbleiben 40 von den insgesamt 206 Datensätzen der Query Flags ungenutzt, die keinen Treffer in der Query Dim Countries haben. Das ist zwar nicht optimal, entspricht aber durchaus der Praxis.

Nach Anwendung des Merge Funktion wird eine sog. Structured Column erzeugt, die die Inhalte der angehängten Tabelle enthält. Jetzt ist mittels der Expand Funktion die Auswahl zu treffen, welche Spalte(n) der angehängten Tabelle tatsächlich extrahiert werden soll. In unserem Fall ist das die Spalte ImageURL:

Abschließend wird die extrahierte Spalte ImageURL ...

... umbenannt auf die gewünschte Spaltenbezeichnung Flag:

Sehr wichtig bei der Merge Query ist die Thematik der Duplikate. Doppelte Ländernamen in der Lookup Tabelle Flags führen auch zu einer Verdoppelung der matchenden Datensätze in der Dim Countries! Daher muß in der Praxis sichergestellt werden, daß es entweder garantiert keine Duplikate in der Lookup Tabelle gibt oder ggfs. ein Remove Duplicates Befehl in der Query Flags angewendet wird. Letzteres ist aber auch supoptimal, da lediglich der erste Datensatz in der aktuellen Sortierung behalten wird und die weiteren dann doppelten Datensätze entfernt werden.

Lösungsvariante 2: Lookup mittels Custom Column

Die alternative Umsetzungsvariante für den Lookup ist eine Custom Column, die im Menü im Bereich Add Column -> Custom Column zu finden ist:

In die Custom Column wird die folgende M-Formel für den Lookup eingesetzt:

try 
Flags[ImageURL]
{List.PositionOf(Flags[Country],[Country name])} 
otherwise 
"-- no match --"

Im Custom Column Dialog sieht das dann so aus:

Daraus wird - anders als bei der Merge Variante - unmittelbar die Ergebnisspalte Flag generiert. Bei Duplikaten in der Query Flags würde der erste Treffer in der aktuellen Sortierung hier ausgegeben werden. Der Datentyp kann effizient durch ein Hinzufügen eines "type text" in die M-Formelzeile bestimmt werden (oder alternativ ein eigener Change Type Step angelegt werden):

Wie funktioniert die M-Formel?

Der Kern dieser Lookup Lösung ist die Funktion List.PositionOf:

{List.PositionOf(Flags[Country],[Country name])}

Diese Funktion erzeugt je Datensatz der Query eine Liste (mit genau 1 Wert je Liste). Diese Listen können probeweise expandiert werden, um die Funktionsweise der List.PositionOf Funktion besser verstehen zu können. Es wird nämlich die Indexposition des Ländernamens in der Lookup Tabelle Flags ermittelt. Bei keinem Treffer wird wird der Indexwert -1 und bei Mehrfachtreffern wird der erste Indexwert in der Lookup Tabelle zurückgegeben:

Hier nochmals die vollständige M-Formel.

• Das Flags[ImageURL] holt aus der Lookup Tabelle den Zielwert für den in der Liste des jeweiligen Datensatzes ermittelten Indexwert
• Das try ... otherwise behandelt den Fall, daß kein Vergleichswert in der Lookup Tabelle gefunden wird (Indexwert = -1)

try 
Flags[ImageURL]
{List.PositionOf(Flags[Country],[Country name])} 
otherwise 
"-- no match --"

Vergleich der beiden Varianten

Die beiden Lösungsvarianten haben recht unterschiedliche Eigenschaften, daher ist die Entscheidung für den Praxiseinsatz idR einfach möglich.

Lookup mit Merge Query:

• 1 Lookup = Mehrere Steps (Merge, Expand, Rename)
• Effizient, wenn beim Lookup mehrere Attribute aus der gleichen Tabelle geholt werden
• Mehrfacheinträge in der Lookup Tabelle -> Vorsicht, es kommt zu einer Vervielfachung auch der Datensätze in der Basistabelle!
• Merge Queries sind performance intensiv und stoppen das Query Folding bei SQL Datenquellen -> Vorsichtiger Einsatz

Lookup mit Custom Columns:

• 1 Lookup = 1 Step (Custom Column)
• Effizient, wenn beim Lookup nur 1 Attribut aus der Tabelle geholt wird
• Mehrfacheinträge in der Lookup Tabelle -> der erste Treffer wird verwertet
• (Die Performancewirkung und das Query Folding wurden hier nicht untersucht)

Quellen

https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9

https://support.microsoft.com/en-us/office/work-with-a-list-record-or-table-structured-column-power-query-d5e552be-c143-4f06-9a5e-0960bbaaf480

https://www.myonlinetraininghub.com/vlookup-in-power-query-using-list-functions