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

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

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

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:

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:

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:

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:

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:

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:

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:

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

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

B. Umgang mit doppelten Einträgen in Power Query

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

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

Danach ist der doppelte Werte entfernt:

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:

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:

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…

… bewältigt Power Query ohne Probleme:

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:

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:

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:

B. Umgang mit doppelten Einträgen in DAX

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

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' ) )

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.

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:

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

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:

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' )

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

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.