Mehrere Tabellen kombinieren mit UNION / UNION ALL in SQL Server, APPEND in Power Query oder UNION in DAX

October 2, 2020
Denis Selimovic
DAX, Microsoft, Power BI, Power Query / M, SQL Servercomment 3Created with Sketch Beta.2 comments

This post is also available in: Englisch

Wenn man mehrere Tabellen miteinander kombinieren möchte, dann stellt sich die Frage, ob man dies bereits in der Datenquelle wie dem SQL Server, in der Aufbereitung der Daten (ETL) in Power Query oder in Power BI machen sollte und vor allem wo genau die Unterschiede liegen.

Vorneweg, der Post ist relativ ausführlich, für alle, denen er zu lang (tl;dr) ist hier schon mal die Vergleichstabelle:

Doppelte einträge bleiben erhalten Doppelte Einträge werden entfernt Ungleiche Anzahl Spalten Abweichende Spaltennamen Ungleiche Reihenfolge der Spalten Kombinieren von mehr als 2 Tabellen
UNION in SQL Server Mit UNION ALL Mit UNION x x
APPEND in Power Query x Mit “Remove duplicates” möglich x x x
UNION in DAX x Mit “DISTINCT” möglich x x
Übersicht der verschiedenen Kombinationsmöglichkeiten

Datentransformation in der Datenquelle oder im Frontend?

Eine Frage, die sich gleich zu Beginn stellt, ist, wo soll das Kombinieren der Tabellen stattfinden?
Grundsätzlich lässt sich das nicht pauschal sagen, da es von mehreren Faktoren abhängt. Aber tendenziell ist es besser dies am Anfang der Datentransformation zu tun. Idealerweise würde das Zusammenfügen somit im SQL Server stattfinden.

Evetuell ist es allerdings nicht ohne weiteres Möglich dies im SQL Server zu tun, sei es weil die Daten dort eventuell noch nicht vorhanden sind, da sie aus verschiedenen Quellen stammen, beispielsweise einer SQL Tabelle und einer CSV Datei. In diesem Fall würde sich die nächste Ebene, hier Power Query innerhalb von Power BI oder die neuen Dataflows anbieten.

Dann gibt es noch die letzte Möglichkeit, die Daten direkt im Power BI mit DAX zusammenzufügen. Auch hier gibt es verschiedene Szenarien, weshalb dies im Power BI Datenmodell geschehen könnte. Beispielsweise wenn eine Tabelle im DAX berechnet wird und somit in Power Query noch gar nicht zur Verfügung steht. Im Normalfall wird man das Zusammenfügen aber weniger im Frontend machen und eher in der Datenquelle oder im Power Query.

Beispieldaten für die 3 Szenarien

Als Beispiel schauen wir uns die Zielwerte für unterschiedliche Lokationen an. Angenommen, dass die Zielwerte aus unterschiedlichen Systemen und somit Tabellen oder Dateien kommen, müssen wir diese für die Analyse wieder zusammenfügen.

Unsere erste Tabelle sieht folgendermaßen aus:

EmpID Name LocationID Location Year Target
1089 Frodo Baggins 12 The Shire 2020 120000
1232 Samwise Gamgee 12 The Shire 2020 140000
1219 Peregrin Took 12 The Shire 2020 100000
1024 Meriadoc Brandybock 12 The Shire 2020 95000
Mitarbeiter der Location “The Shire” mit ihren Zielwerten

Diese wollen wir mit der Tabelle unserer zweiten Lokation zusammenführen, um eine vollständige Tabelle aller Lokationen zu erhalten. Man beachte, dass wir in der zweiten Tabelle eine Spalte mehr haben, da hier mehrere Regionen zu der Lokation bzw. zu einem gemeinsamen Cluster gehören:

EmpID Name LocationID Location Cluster Year Target
9042 Aragorn 8 Minas Tirith Central Middle Earth 2020 260000
9058 Arwen 8 Minas Tirith Central Middle Earth 2020 190000
9012 Faramir 8 Minas Tirith Central Middle Earth 2020 210000
9276 Damrod 8 Minas Tirith Central Middle Earth 2020 180000
5412 Theoden 16 Rohan Central Middle Earth 2020 220000
5419 Eomer 16 Rohan Central Middle Earth 2020 180000
5487 Eowyn 16 Rohan Central Middle Earth 2020 210000
5876 Eothain 16 Rohan Central Middle Earth 2020 185000
5912 Haleth 16 Rohan Central Middle Earth 2020 150000
5001 Gamling 16 Rohan Central Middle Earth 2020 165000
1089 Frodo Baggins 12 The Shire 2020 120000
Mitarbeiter des Clusters “Central Middle Earth” mit ihren Zielwerten

Wichtig für dieses Szenario ist der letzte Datensatz aus dem Cluster “Central Middle Earth”. Der Mitarbeiter “1089 Frodo Baggins” stammt eigentlich aus der Region unserer ersten Tabelle, war beruflich unter dem Jahr allerdings in der Region “Rohan”/”Minas Tirith” unterwegs, weshalb der Manager dort einen Zielwert eingetragen hat. Da er mittlerweile aber wieder zurück in “The Shire” ist, hat der dortige Manager ihn ebenfalls in sein System eingetragen. Entsprechend haben wir hier einen doppelten Datensatz.

Für unsere Testreihe benötigen wir dann noch eine dritte Tabelle, hier werden wir die Zielwerte der Lokation “Moria” verwenden:

EmpID Name LocationID Location Year Target
7431 Balin 14 Moria 2020 245000
7129 Dwalin 14 Moria 2020 178000
7194 Gimli  14 Moria 2020 210000
7225 Ori 14 Moria 2020 190000

Wir werden die einzelnen Versionen auf 6 Kriterien testen:

  1. Syntax zum Kombinieren der Tabellen
  2. Umgang mit doppelten Einträgen
  3. Verhalten bei ungleicher Anzahl Spalten
  4. Verhalten bei abweichenden Spaltennamen
  5. Verhalten bei ungleicher Reihenfolge der Spalten
  6. Verhalten bei mehr als 2 Tabellen

1. UNION / UNION ALL in SQL Server

Starten wir mit dem zusammenführen im SQL Server.

A. Syntax zum Kombinieren der Tabellen in SQL Server

Die Syntax ist relativ simpel, wir kombinieren die Select Abfragen der einzelnen Tabellen mit einem UNION oder UNION ALL

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetShire]

UNION

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetCentralMiddleEarth]

Als Ergebnis erhalten wir die zusammengeführten Tabellen:

image 23

B. Umgang mit doppelten Einträgen in SQL Server

Wenn man genau aufgepasst hat, dann fehlt im gerade ausgeführten Beispiel der doppelte Eintrag des Mitarbeiters “Frodo Baggins”. Dies liegt daran, dass der “UNION” Befehl doppelte Werte entfernt.

Wenn wir auch die doppelten Einträge haben möchten, dann müssen wir “UNION ALL” verwenden. Die Syntax ist analog zum oberen Beispiel:

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetShire]

UNION ALL

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetCentralMiddleEarth]

Somit erhalten wir auch den doppelten Datensatz:

image 25

C. Verhalten bei ungleicher Anzahl Spalten in SQL Server

Im SQL Server kommen wir hier an ein Limit, denn die Anzahl Spalten muss zwangsläufig immer identisch sein. Ansonsten laufen wir in einen Fehler, wenn wir beispielsweise versuchen den Cluster aus der zweiten Tabelle zu integrieren:

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetShire]

UNION ALL

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Cluster]
	,[Year]
	,[Target]
FROM [TargetCentralMiddleEarth]

Hier stoßen wir auf den folgenden Fehler:

Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Eine zusätzliche Spalte müsste in den restlichen Queries abgefangen werden (NULL AS [Cluster]).

D. Verhalten bei abweichenden Spaltennamen in SQL Server

Abweichende Spaltennamen sind hingegen kein Problem. Es werden grundsätzlich die Spaltennamen der ersten Query verwendet. Die folgenden Queries müssen nur noch die korrekte Anzahl Spalten aufweisen:

SELECT [EmpID] AS [EmployeeID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetShire]

UNION ALL

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Cluster]
	,[Year]
	,[Target]
FROM [TargetCentralMiddleEarth]

Man beachte, dass sich der Spaltenname der ersten Spalte an die Bezeichnung des ersten Select anpasst:

image 27

E. Verhalten bei ungleicher Reihenfolge der Spalten in SQL Server

Wie gerade schon erwähnt orientiert sich der SQL Server an den Spalten des ersten Select. Die Spaltenbezeichnung der folgenden Queries werden ignoriert. Wenn sich entsprechend die Reihenfolge in der folgenden Query ändert, werden die Werte in der Reihenfolge des Erscheinens im Select “blind” übernommen, soweit die Dateitypen übereinstimmen:

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetShire]

UNION ALL

SELECT [LocationID]
	,[Name]
	,[EmpID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetCentralMiddleEarth]

Die Werte der LocationID und der EmpID tauchen somit gemischt in den Spalten auf:

image 28

Falls wir eine Spalte mit Text und eine Spalte mit Zahlenwerten auf die entsprechenden Positionen setzen würden, dann würden wir in einen Conversion-Fehler laufen:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Frodo Baggins' to data type int.

F. Verhalten bei mehr als 2 Tabellen in SQL Server

Es lässt sich eine unbegrenzte Zahl von Tabellen aneinander fügen. Hierzu müssen einfach weitere UNION bzw. UNION ALL Statements an ein ander gereiht werden:

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetShire]

UNION ALL

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetCentralMiddleEarth]

UNION ALL

SELECT [EmpID]
	,[Name]
	,[LocationID]
	,[Location]
	,[Year]
	,[Target]
FROM [TargetMoria]

Jede weitere Tabelle wird einfach unter die vorhandenen hinzugefügt:

image 29

2. APPEND in Power Query

In Power Query ist das Verhalten etwas anders als im SQL Server. Hier werden die Spalten ausschließlich über die Namen zusammengefügt und nicht über die Reihenfolge, wodurch sich andere Möglichkeiten ergeben.

A. Syntax zum Kombinieren der Tabellen in Power Query

Aufgrund der visuellen Arbeitsweise von Power Query wäre es etwas ungewöhnlich die programmatische Syntax in M (= der Abfragesprache von Power Query) zu verwenden. Ich werde mich daher auf die Erstellung der Query mit der graphischen Oberfläche fokussieren.

Zum Erstellen der Query wird die erste gewünschte Tabelle markiert und über die Append-Funktion zusammengefügt:

image 30

Danach muss nur noch die zweite Tabelle gewählt werden:

image 31

Danach ist die Zusammenführung auch schon vollbracht, im Power Query könnten nun weitere Transformationen gemacht werden:

image 32

B. Umgang mit doppelten Einträgen in Power Query

Duplikate werden bei einem Append nicht automatisch herausgefiltert. Diese existieren weiterhin:

image 33

Diese lassen sich allerdings in einem nächsten Schritt über die Duplikate Entfernen Funktion herausfiltern:

image 34

Danach ist der doppelte Werte entfernt:

image 35

C. Verhalten bei ungleicher Anzahl Spalten in Power Query

Wie bereits erwähnt, geht der Append in Power Query über die Spaltennamen. Spalten mit demselben Namen werden zusammengelegt, Spalten, die einer Tabelle fehlen, werden einfach leer gelassen:

image 36

D. Verhalten bei abweichenden Spaltennamen in Power Query

Ebenso verhält es sich bei Spaltennamen. Wenn sich der Name ändert, wird der Inhalt nicht zusammengefügt.

Wie im vorherigen Beispiel wurde bei der ersten Tabelle die Spalte “EmpID” in “EmployeeID” umbenannt, bei der zweiten Tabelle wird der Name beibehalten:

image 37

E. Verhalten bei ungleicher Reihenfolge der Spalten in Power Query

Die Reihenfolge spielt in Power Query überhaupt keine Rolle, da der Append ausschliesslich über den Spaltennamen stattindet.

Der Append der folgenden beiden Tabellen…

image 38
image 40

… bewältigt Power Query ohne Probleme:

image 41

F. Verhalten bei mehr als 2 Tabellen in Power Query

Auch das Zusammenführen mehrerer Tabellen ist problemlos möglich. Über die Möglichkeit mehr als 2 Tabellen zusammenzuführen, können alle angegebenen Tabellen kombiniert werden:

image 42
image 43

3. UNION in DAX

Obwohl man es in der Praxis seltener vorfindet, können in DAX nicht nur Calculated Columns und Calculated Measure erstellt werden, sondern auch Caculated Tables.

Diese werden im Modeling Tab über “New Table” erstellt:

image 44

Ein großer Unterschied zu den anderen beiden Vorgehen ist sicherlich, dass der UNION Befehl in DAX vorhandene Tabellen aus dem Datenmodell zusammenfügt. Dies bedeutet, dass die Basistabellen zwar vor dem Endanwender ausgeblendet werden können, allerdings werden diese Trotzdem ins Datenmodell geladen. Zusammen mit der Aufbereitung der Daten beim Initialisieren des Modells vervielfältigt sich auf jeden Fall der Speicherplatz der verwendeten Tabellen, da diese nochmals in der UNION-Tabelle vorhanden sind.

A. Syntax zum Kombinieren der Tabellen in DAX

Die Syntax ist auch hier relativ einfach gehalten. Mit der UNION Funktion werden einfach die genannten Tabellen zusammengefügt:

A Union = UNION('A TargetShire', 'A TargetCentralMiddleEarth')

Als Ergebnis erhalten wir die zusammengefügte Tabelle direkt im Power BI:

image 45

B. Umgang mit doppelten Einträgen in DAX

Wie gerade im ersten Szenario gesehen werden doppelte Einträge nicht herausgefiltert:

image 46

Das UNION in DAX entspricht somit einem UNION ALL in SQL bzw. einem APPEND in Power Query. Wenn man den zusätzlichen Eintrag eliminieren möchte, lässt sich dies mit der DISTINCT Funktion erreichen:

A Union Distinct = DISTINCT( UNION( 'A TargetShire', 'A TargetCentralMiddleEarth' ) )
image 47

C. Verhalten bei ungleicher Anzahl Spalten in DAX

Analog zum SQL benötigt auch DAX die identische Anzahl Spalten. In Szenario C können wir die beiden Tabellen mit einer ungleichen Anzahl Spalten leider nicht verbinden:

Each table argument of 'UNION' must have the same number of columns.
image 48

Hier müsste die Tabelle mit weniger Spalten über eine Tabellenfunktion in DAX als Workaround manipuliert werden.

D. Verhalten bei abweichenden Spaltennamen in DAX

Auch die Namensgebung der Spalten verhält sich wie im SQL Server. Es werden einfach die Namen der ersten Tabelle verwendet:

image 49

Entsprechend wird die Spaltenbezeichnung geändert, wenn man die Reihenfolge der Tabellen beim UNION ändert:

image 51

E. Verhalten bei ungleicher Reihenfolge der Spalten in DAX

Wie im SQL Server zählt auch im DAX nur die Reihenfolge der Spalten. Sobald also die Spalten nicht in identischer Reihenfolge sind, werden die Inhalte vermischt:

image 52

F. Verhalten bei mehr als 2 Tabellen in DAX

Die Syntax von UNION benötigt mindestens 2 Tabellen zum Kombinieren, optional können aber beliebig viele Tabellen zusammengefügt werden. Diese lassen sich einfach als weiteres Argument der Funktion hinzufügen. Wichtig ist hierbei, dass alle Tabellen einen identischen Aufbau haben müssen, um ein gewünschtes Ergebnis zu erhalten:

F Union = UNION( 'F TargetShire', 'F TargetCentralMiddleEarthDAX', 'F TargetMoria' )
image 53

Fazit

Kommen wir zur Zusammenfassung. Das Kombinieren mehrerer Tabellen zu einer großen Tabelle ist auf alle 3 Arten möglich. Wie wir gesehen haben ist das Verhalten von UNION im SQL Server und UNION in DAX innerhalb vom Power BI sehr ähnlich. Hier werden einfach Tabellen mit derselben Anzahl Spalten direkt unter einander gefügt. Die APPEND Funktion in Power Query geht einen anderen Weg und kombiniert die Tabellen anhand der Spaltennamen.

Das Verhalten möchte ich nochmal in tabellarischer Form zusammenfassen:

Doppelte einträge bleiben erhalten Doppelte Einträge werden entfernt Ungleiche Anzahl Spalten Abweichende Spaltennamen Ungleiche Reihenfolge der Spalten Kombinieren von mehr als 2 Tabellen
UNION in SQL Server Mit UNION ALL Mit UNION x x
APPEND in Power Query x Mit “Remove duplicates” möglich x x x
UNION in DAX x Mit “DISTINCT” möglich x x
Übersicht der verschiedenen Kombinationsmöglichkeiten

Bleibt am Ende noch die Frage welchen Befehl man verwenden sollte. Wie bereits in der Einleitung geschrieben kommt das stark auf den Kontext an. Alle drei Ansätze haben ein unterschiedliches Verhalten. Abhängig von der Ausgangslage muss jeder Entwickler selbst entscheiden, wo es am meisten Sinn ergibt.