Eigene Datentypen mit Power Query erstellen

Vor wenigen Tagen – am 02.09.2020, um genau zu sein – hat das Office Insider Team die Verfügbarkeit der Power Query Datentypen bekannt gegeben. Das Feature ist derzeit – Stand September 2020 – auf den Enterprise-Plan E5 und den Education-Plan A5 von Office 365 bzw. Microsoft 365 beschränkt. Es wird zudem eine Excel-Version im Betakanal ab Version 2009 und dem Build 13226.20002 vorausgesetzt. Das Ausrollen der neuen Funktionalität findet auch hier wieder schrittweise statt, weshalb es vorkommen kann, dass der eine oder andere die Power Query Datentypen trotz Erfüllens der Voraussetzungen noch nicht hat. Hier ist dann etwas Geduld gefragt.

Mit den Power Query Datentypen steht nun eine vierte Gruppe von Datentypen in Excel zur Verfügung. Den Anfang haben vor einiger Zeit die beiden Datentypen zu Aktien & Geografie gemacht, die beide auf einer Bing-Abfrage basieren. Die zweite Gruppe bilden die Power-BI-Datentypen, die auf benutzerdefinierten Datensets in Power BI beruhen. Die dritte Gruppe bilden die sogenannten Wolfram-Datentypen, die auf Abfragen an Wolfram-Alpha beruhen.

Die vierte Gruppe sind nun die Power Query Datentypen, die Inhalt dieses Artikels sind. Auch für mich sind die Power Query Datentypen neu und somit habe ich in diesem Artikel sicherlich noch nicht alle Möglichkeiten ausgelotet, die sich nun ergeben. Betrachten Sie somit diesen Artikel als eine Art Einführung in diesen Datentyp.

Kurze Einführung zu den Datentypen in Excel

Eine detaillierte Beschreibung zu der Funktionalität einzelner Datentypen habe ich in meinen beiden vorangegangenen Artikeln Datentypen aus Power BI in Excel abrufen und Wolfram Datentypen in Excel verwenden aufgeführt. Von daher nachfolgend nur eine kurze Zusammenfassung der Features von Datentypen in Excel.

Generell weisen alle Datentypen ein ähnliches Grundprinzip in Bedienung und Struktur auf. Einige sind mit etwas mehr Features – wie beispielsweise Hierarchien die Wolfram-Datentypen – ausgestattet. Die Zuweisung eines Datentyps zu einer Zelle gestaltet sich sehr einfach: Sie markieren eine Zelle oder einen Bereich mit Inhalten in einem Arbeitsblatt. Anschließend weisen der Zelle bzw. dem Bereich einen Datentyp zu, indem Sie einen passenden Datentyp aus der Auswahlliste innerhalb der Gruppe Datentypen im Reiter Daten des Menübands auswählen.

Ist die Zuweisung des Datentyps erfolgreich, wird am linken Rand der Zelle ein Symbol angezeigt, welches sich von Datentyp zu Datentyp unterscheiden kann. Ein Klick auf das Symbol ruft eine sogenannte Datenkarte auf, die die Eigenschaften bzw. Felder des Datentyps anzeigt und die es ermöglicht deren Inhalt in Zellen einzufügen. Hierbei wird der Inhalt nicht statisch, sondern als Formel eingefügt. Folgende Abbildung zeigt beispielhaft die Datenkarte zu der Stadt Aachen aus dem Datentyp Geografie an. Das Land (Germany) wurde via einer Formel abgerufen.

Zu beachten ist, dass die von Microsoft zur Verfügung gestellten Datentypen derzeit noch mit englischen Feldnamen arbeiten. Zudem sind auch die zurückgegebenen Inhalte meist in Englisch. Das trifft natürlich nicht auf benutzerdefinierte Datentypen zu, wie z.B. eigene Power BI Datentypen.

Erstellung der Power Query Abfragen

Als Ausgangsdaten für die nachfolgende Erstellung von Power Query Abfragen und Datentypen habe ich zwei Excel-Arbeitsmappen verwendet, die je im ersten Arbeitsblatt eine intelligente Tabelle beinhalten. Die erste Mappe enthält eine einfache Liste mit Abteilungsdaten und die zweite Mappe eine Tabelle mit Personendaten.

Ziel wird sein, aus den Personendaten einen Power Query Datentyp zu erstellen, der später in der Arbeitsmappe als Liste abgelegt ist und in Formeln verwendet werden kann.

Die Tabelle zu den Abteilungsdaten enthält die drei Spalten Nummer, Kürzel und Bezeichnung. Die Tabelle zu den Personen enthält je eine Spalte zu der Personalnummer, zum Vor- und Nachnamen, zur telefonischen Durchwahl, zum Geburtsdatum und zu einem Verweis auf die Abteilungsnummer. Alle Daten sind fiktiv und entsprechen somit nicht realen Personen.

Zu diesen beide Mappen habe ich jeweils eine Power Query Abfrage erstellt. In der Abfrage zu den Personendaten habe ich die Angabe zu der Abteilung mit den Daten aus der Abfrage zu der Abteilung zusammengeführt sowie die Ergebnisspalten lesefreundlicher umbenannt.

Lade ich nun das Ergebnis der Abfrage zu den Personen in die Arbeitsmappe, präsentiert sich die Tabelle wie folgt abgebildet. Alle Spalten der Abfrage werden in dieser Tabelle aufgeführt.

Power Query Datentypen erstellen

Um einen Power Query Datentyp zu erstellen, muss wieder der Power Query Editor aufgerufen werden. Das geht am beispielsweise über einen Doppelklick auf einer der Abfragen im entsprechenden Aufgabenbereich. Da der Datentyp aus der Abfrage zu den Personen erstellt werden soll, muss die Abfrage zunächst im Power Query Editor ausgewählt werden. Anschließend sind die Spalten in der Abfrage, die dem Datentyp hinzugefügt werden sollen, auszuwählen. Schließlich ist der Befehl Datentyp erstellen aus der Gruppe Strukturierte Spalte im Reiter Transformieren des Menübands aufzurufen. Alternativ kann auch der entsprechende Befehl im Kontextmenü einer Spalte verwendet werden.

Es erscheint ein Dialog, der die Angabe eines Namens für den Datentyp sowie Auswahl der Spalte zulässt, die später in der Ausgabetabelle angezeigt werden soll. In der erweiterten Version des Dialogs lassen sich gegebenenfalls auch Spalten zum Datentyp manuell hinzufügen oder entfernen.

Für die Erstellung des ersten Datentyps habe ich alle Spalten bis auf die Spalten zu der Abteilung ausgewählt und den Datentyp als Personendatentyp benannt. Die Vorschauansicht im Power Query Editor präsentiert sich dann wie folgt zu sehen.

Die ausgewählten Spalten werden dann wie eine Gruppierung zusammengefasst. Interessant ist die neue M-Code-Funktion Table.CombineColumnsToRecord, auf die ich später nochmal zurückkommen werde. Aktualisiere ich nun die Ausgabetabelle im Arbeitsblatt, erscheinen nicht mehr alle Spalten der Abfrage, sondern es wird der definierte Datentyp angezeigt. Wir bei den anderen Datentypen auch, lässt sich für einen Eintrag eine Datenkarte abrufen, die den Inhalt der dem Datentyp zugeordneten Spalten anzeigt.

Power Query Datentypen verwenden

Einzelne Felder zu Datentyp lassen sich natürlich auch per Formeln abrufen, wie nachfolgend abgebildet zu sehen. Das Feld Title in der IntelliSense-Liste scheint übrigens eine Referenz auf eine intern vergebene Bezeichnung zu sein.

Neben der direkten Adressierung lässt sich auch die Excel-Funktion FELDWERT zum Abruf eines Felds zum Datentyp verwenden. Und, sehr interessant, auch die Funktion INDEX und weitere Funktionen sind dazu in der Lage, den Datentyp im zurückgegebenen Ergebnis direkt zu adressieren. Beispielsweise geben folgende Formeln jeweils dasselbe dynamische Array mit allen Vornamen zurück:

E2 = Personenabfrage[Personendatentyp].[Vorname]
E2 = FELDWERT(Personenabfrage[Personendatentyp];"Vorname")
E2 = INDEX(Personenabfrage[Personendatentyp];0;1).[Vorname]

Wenn nun das Erstellen eines Datentyps die Möglichkeit zulässt, diesem eine benutzerdefinierte Auswahl an Spalten zuzuweisen, müsste von meinem Verständnis das Erstellen mehrerer Datentypen ebenfalls zulässig sein. Das habe ich mal ausprobiert – und tatsächlich, es geht. So habe ich aus den beiden letzten Spalten zur Abteilungsangabe ebenfalls einen Datentyp erstellt. In der Vorschau im Power Query Editor sieht das dann wie folgt abgebildet aus.

Eine Aktualisierung der Ausgabetabelle in der Arbeitsmappe führt dann zu einer weiteren Reduzierung der Spalten auf nun zwei Spalten, beides Datentypen.

Auch hieraus ergeben sich dann interessante Formeln, wie beispielsweise die folgende XVERWEIS-Formel, die nach dem Eintrag "1005" innerhalb des Datentyps zu den Personen die Personalnummer heraussucht und den Datentyp zu den Abteilungen zurückliefert:

=XVERWEIS("1005";Personenabfrage[Personendatentyp].[Personalnummer];
Personenabfrage[Abteilungsdatentyp])

Möchte ich statt der XVERWEIS-Funktion lieber einen INDEX/VERGLEICH verwenden, geht das natürlich auch und sieht wie folgt aus:

=INDEX(Personenabfrage[Abteilungsdatentyp];
 VERGLEICH("1005";Personenabfrage[Personendatentyp].[Personalnummer];0);1)


Zu beachten ist wie beim XVERWEIS, dass als Suchmatrix die Spalte innerhalb des Datentyps angesprochen wird. Würde ich nur Personenabfrage[Personendatentyp] als zweites Argument in der Funktion VERGLEICH angeben, funktioniert das nicht.

Aus dem vorangegangen Beispiel lässt sich somit eine einfache Personalsuche erstellen – siehe die erste Abbidlung in diesem Artikel – wo zu einer Personalnummer die entsprechenden Daten zu der Person gesucht und in Zellen ausgegeben werden.

Anschließend habe ich in Power Query versucht, Abfragen zusammenzuführen, von denen mindestens eine Abfrage einen Datentyp enthält. Leider erkennt der Dialog zum Zusammenführen von Abfragen die Datentyp-Spalten bislang noch nicht. Es müsste somit eine separate Abfrage auf den Datentyp erstellt werden, die den Datentyp wieder expandiert und dann als Quelle für das Zusammenführen verwendet wird. Aternativ kann dies auch per M-Code-Eingabe im erweiterten Editor durchgeführt werden.

M-Code zu Power Query Datentypen

Zur Erstellung eines Datentyps wird im M-Code die Funktion Table.CombineColumnsToRecord() verwendet, dessen Syntax wie folgt aussieht:

Table.CombineColumnsToRecord(table as table, newColumnName as text, sourceColumns as list, optional options as nullable record) as table

Noch ist bei MS Docs nicht viel an Dokumentation zu dieser Funktion vorhanden. Zum Beispiel fehlen noch derzeit eine Liste der möglichen Optionen oder Anwendungsbeispiele. Der Aufruf im von Power Query generierten M-Code sieht für den Personendatentyp wie folgt aus:

Table.CombineColumnsToRecord(#"Anwendungsschritt", "Personendatentyp", {"Personalnummer", "Vorname", "Nachname", "Durchwahl", "Geburtsdatum"}, [DisplayNameColumn="Personalnummer", TypeName="Excel.DataType"])

Das erste Argument ist ein Bezug auf eine Tabelle, hier also der vorherige Anwendungsschritt. Es folgt die Bezeichnung des Datentyps, eine Liste an Spalten, die dem Datentyp hinzugefügt werden sollen und die Angabe optionaler Parameter. Der Parameter DisplayNameColumn legt fest, welche Spalte aus dem Datentyp in der Ausgabetabelle in Excel angezeigt werden soll. Der zweite Parameter TypeName legt eine Typbezeichnung fest, die nicht geändert werden sollte.

Nun ist die Liste der Spalten im oberen Beispiel als statische Liste von Werten angegeben. Es kann ja aber auch mal die Situation vorkommen, dass Sie alle Spalten der Tabelle in einen Datentyp umwandeln möchten. In einem solchen Fall lässt sich über den erweiterten Editor im M-Code die Liste an Spalten durch Table.ColumnNames(#“Anwendungsschritt“) ersetzen, was dynamisch alle Spaltennamen ermittelt. Soll jetzt auch noch immer die erste Spalte als Anzeigespalte verwendet werden, lässt sich der Parameter DisplayNameColumn ebenfalls dynamisieren, indem dann DisplayNameColumn=Table.ColumnNames(#“Anwendungsschritt“){0} angeben wird. Der M-Code sähe dann wie folgt aus:

=Table.CombineColumnsToRecord(#"Anwendungsschritt", "Personendatentyp",
 Table.ColumnNames(#"Anwendungsschritt"), [
 DisplayNameColumn=Table.ColumnNames(#"Anwendungsschritt"){0},
 TypeName="Excel.DataType"])


Fazit

Das neue Feature, eigene Datentypen in Power Query definieren zu können, eröffnet vielfältige neue Anwendungsmöglichkeiten, wie auch die Power BI-Datentypen. Kombiniert mit Excel-Formeln und dynamischen Arrays ergeben sich mächtige Funktionen, die vor ein paar Jahren wahrscheinlich kaum jemand auf dem Schirm hatte. Ich kann dazu nur sagen: Wow, Excel-Team, well done!

Wie auch die anderen Datentypen, befinden sich die Power Query Datentypen in einer Vorschau, was denn heißt, dass die Implementierung weiterer Fähigkeiten durchaus zu erwarten ist. Ich denke da beispielsweise an Hierarchien oder, was ja bislang noch nicht möglich ist, das Zusammenführen von Abfragen, die Datentypen enthalten.

Wünschen würde ich mir, dass auch hier MS das derzeitige Lizenzmodell zu Power Query Datentypen überdenkt und die Funktionalität für mehr User freigibt – so wie für Power BI Datentypen geschehen.

Happy Exceling :-)