Eigene Datentypen definieren in Power Query 4

Ein relativ neues Feature in Power Query eröffnet interessante Möglichkeiten
 

Vor ein paar Wochen habe ich in einem Artikel die in Microsoft 365 integrierten Datentypen vorgestellt (falls du den Artikel verpasst hast, solltest du ihn hier gleich nachlesen). Eine weitere und wie ich finde sehr spannende Möglichkeit ist es, völlig eigene Datentypen zu definieren.

Alles, was man dazu braucht, ist Excel aus Microsoft 365/Office 365 und Power Query.

Und wie das funktioniert, zeige ich dir in diesem Beitrag.

Einen eigenen Datentyp erstellen

Als Beispiel verwende ich einen kleinen Artikelstamm, der aus der Northwind-Musterdatenbank von Microsoft stammt (die Beispieldatei kannst du dir hier herunterladen). Die Daten habe ich als formatierte Tabelle angelegt und dafür den Namen „tblProdukte“ vergeben:

Der Artikelstamm als formatierte Tabelle

Der Artikelstamm als formatierte Tabelle

Als nächstes wird die Tabelle nach Power Query geladen: Menü „Daten | Aus Tabelle/Bereich“:

Tabelle nach Power Query laden

Tabelle nach Power Query laden

Im Power Query-Editor sehen wir dann die komplette Artikeltabelle. Es empfiehlt sich grundsätzlich, die automatisch gesetzten Datentypen für jede Spalte zu kontrollieren und ggf. anzupassen. In meinem Beispiel wurden aber alle Spalten korrekt zugeordnet. Nun kommen wir auch schon zum eigentlichen Punkt, der Erstellung eines benutzerdefinierten Datentyps. Da das Hauptfeld die Artikelnummer sein soll, muss die Spalte „Artikel-Nr.“ markiert werden. Im Menü „Transformieren“ findet sich dann am ganz rechten Rand die Schaltfläche „Datentyp erstellen“:

Artikel-Nr markieren und Datentyp erstellen

Artikel-Nr markieren und Datentyp erstellen

Nach einem Klick darauf öffnet sich ein weiteres Fenster. Hier ändern wir zuerst den Datentypnamen in „Produkt“ und wählen das die Option „Weitere“:

Fenster "Datentyp erstellen"

Fenster „Datentyp erstellen“

Dadurch erscheint um unteren Fenster die Liste aller Felder unserer Artikeltabelle. Wir wollen nur bestimmte Spalten in unseren Datentyp aufnehmen und markieren dort deshalb bei gedrückter Strg-Taste die ersten 5 Felder und übertragen diese mit einem Klick auf „Hinzufügen“ in die rechte Liste:

Benötigte Spalten auswählen

Benötigte Spalten auswählen

Anschließend schließen wir das Datentypfenster mit OK. Die Datentabelle hat sich nun verändert. Neben der Artikelnummer sind nur noch die Spalten zu sehen, die wir nicht in den Datentyp übernommen haben. Und die Artikelnummer heißt nun „Produkte“ und hat außerdem ein neues Symbol erhalten, welches anzeigt, dass es sich hier um einen Datentyp handelt:

Die Artikelspalte ist jetzt ein Datentyp

Die Artikelspalte ist jetzt ein Datentyp

Als nächstes löschen wir die nicht benötigten Spalten „Lagerbestand“, „Bestellte Einheiten“, „Mindestbestand“ und „Auslaufartikel“, so dass nur noch die „Produkte“-Spalte übrig bleibt. Und damit sind alle Vorarbeiten abgeschlossen, so dass wir den Power Query-Editor über das Menü „Start | Schließen & Laden“ wieder verlassen können:

Abfrage schließen und laden

Abfrage schließen und laden

In Excel wird die Produkteliste in ein neues Arbeitsblatt geladen, wo man an dem vorangestellten Symbol vor der Artikelnummer schon erkennen kann, dass es sich hierbei um einen Datentyp handelt. Wenn man nun bei einem beliebigen Produkt auf dieses Symbol klickt, wird die Datentyp-Karte mit allen Details für diesen Artikel eingeblendet:

Die Tabelle mit den neuen Datentyp

Die Tabelle mit den neuen Datentyp

Informationskarte wird bei Klick angezeigt

Informationskarte wird bei Klick angezeigt

Nicht schlecht, oder?

Arbeiten mit dem neuen Datentyp

Und wie du sicherlich schon vermutet hast, lassen sich die einzelnen Informationen natürlich auch gezielt wieder in die Tabelle übernehmen. Wenn man auf das kleine Symbol in der rechten oberen Ecke der Artikeltabelle klickt, erhält man die Liste alle enthaltenen Datenfelder und kann dort bequem das gewünschte auswählen. Die betreffende Information wird sofort für alle Artikel in der Tabelle übernommen:

Die Liste aller enthaltenen Felder

Die Liste aller enthaltenen Felder

Artikelname wurde übernommen

Artikelname wurde übernommen

Alternativ kann ich auch in eine beliebige leere Zelle neben der Artikelliste ein Gleichheitszeichen eintippen und auf die Artikelnummer in der Produktespalte klicken. Dadurch wird mir wieder die Liste aller Datentypfelder angezeigt, aus der ich den gewünschten mit einem Doppelklick übernehmen kann. Und wer auf die Maus verzichten möchte, kann einfach einen Punkt nach dem Produkt eingeben und den Namen der gewünschten Spalte eintippen und mit der Tabulatortaste übernehmen:

Neue Datenfelder werden übernommen

Neue Datenfelder werden übernommen

Aber es kommt noch viel besser!

Filtern nach ausgeblendeten Attributen

Sehr interessant ist die Möglichkeit, die Produktetabelle nach bestimmten Attributen zu filtern, obwohl diese gar nicht sichtbar sind. Ich lösche dazu nochmal alle zuvor eingefügten Spalten, so dass nur die Produkte-Spalte angezeigt wird. Nun möchte ich nur solche Produkte ausgeben lassen, die zu einer bestimmten Kategorie gehören, die ich in Zelle D2 eingebe:

Vorbereiten der Filterausgabe

Vorbereiten der Filterausgabe

In Zelle F2 kommt nun die folgende Formel:
=FILTER(tblProdukte_2[Produkte];tblProdukte_2[Produkte].Kategorie=D1)

Die FILTER-Funktion im Einsatz

Die FILTER-Funktion im Einsatz

Bei der Eingabe kann ich mir selbstverständlich wieder durch Klicken mit der Maus helfen lassen:

Dynamische FILTER-Ausgabe nach Kategorie

Dynamische FILTER-Ausgabe nach Kategorie


Da es sich bei FILTER um eine der neuen dynamischen Arrayfunktionen handelt, verändert sich der Ausgabebereich dynamisch und wird somit automatisch länger oder kürzer, je nachdem, wie viele Produkte eben in der Kategorie vorhanden sind. Und wie man an dem Symbol sieht, handelt es sich auch bei der gefilterten Liste wieder um Datentypen.

Treiben wir das Ganze noch ein wenig weiter und lassen daneben die Artikelnamen ausgeben. Damit sich auch diese dynamisch an die Länge der Produkteliste anpasst, muss hinter den Zellenbezug F2 das #-Zeichen eingegeben werden:
=F2#.Artikelname

Dieses #-Zeichen besagt, dass es sich bei der angegebenen Zelle um ein dynamisches Array handeln soll, und nicht nur um eine einzelne Zelle:

Dynamische Ausgabe der Artikelnamen

Dynamische Ausgabe der Artikelnamen

Ziemlich genial, wie ich finde.

Das soll es für heute gewesen sein, auch wenn es zu diesem Thema sicherlich noch einiges zu erzählen gäbe. Mehr dazu vielleicht ein anderes mal. Du hast sicherlich schon ein paar Ideen, wie du diese Datentypen nutzen kannst, oder? Falls ja, lass es uns in den Kommentaren wissen.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

4 Gedanken zu “Eigene Datentypen definieren in Power Query

  • Sebastian

    Hallo Martin.

    Vielen Dank, für diese verständlichen Informationen zu der tatsächlich echt coolen Anwendungsmöglichkeit von Datentypen.
    Achja und überhaupt zu den Themen Power Query und Power Pivot, die ich nun immer mehr in der Praxis verwende.

    Diesen Artikel habe ich zum Anlass genommen, um Datentypen in einem aktuellen Projekt einzubauen und stelle mir die Frage, ob und wenn ja, wie ich gleich mehrere Kategorien filtern kann.

    Aktuell lautet meine Formel:
    =FILTER(DB_Prozesse_2;DB_Prozesse_2[Prozess].[DB_Teilprozesse.bet-TP1]=K1)

    Das bei mir die eckigen Klammern auch bei der Auswahl der Spalte dran stehen, liegt vermutlich an einem zuvor durchgeführten join von zwei Tabellen über Power Query. Wenn ich eine Spalte Abfrage, die sich in der anderen Tabelle befand, stehen die Klammern nicht da.

    Aber meine eigentliche Frage:
    Ich möchte hier filtern, in welchen Prozessen der Tabelle ein bestimmter Teilprozess (TP) vorkommt. Diese können in den Spalten DB_Teilprozesse.bet-TP1 – 15 liegen. Zurzeit kann ich nur etwas finden, wenn es sich um den ersten Teilprozess handelt.

    Das klassische [[Spalte]:[Spalte]] hat hier nicht geklappt.
    Vielleicht kannst du mir helfen?

    Vielen Dank und weiter so 🙂

    • Martin Weiß Autor des Beitrags

      Hallo Sebastian,

      vielen Dank für das schöne Feedback und freut es mich natürlich, dass du die Datentypen gleich mal bei dir einsetzt! Nun zu deiner Frage zur FILTER-Funktion.

      Wenn ich es richtig verstehe, dann kann der Teilprozess also in verschiedenen Spalten vorkommen, was also einer ODER-Bedingung entspricht (Teilprozess enthalten in Spalte1 oder Spalte2 oder in Spalte3…)
      Das lässt sich in der FILTER-Funktion ohne Probleme abbilden.

      Mehrere ODER-Kriterien werden dabei in Klammern gesetzt und über das Pluszeichen verbunden, bei deinem Beispiel also
      =FILTER(DB_Prozesse_2;(DB_Prozesse_2[Prozess].[DB_Teilprozesse.bet-TP1]=K1)+(DB_Prozesse_2[Prozess].[DB_Teilprozesse.bet-TP2]=K1)+(DB_Prozesse_2[Prozess].[DB_Teilprozesse.bet-TP3]=K1))

      Brauchst du hingegen eine UND-Bedingung (das Kriterium muss also gleichzeitig in drei Spalten enthalten sein), dann verwendest du eine Multiplikation:
      =FILTER(DB_Prozesse_2;(DB_Prozesse_2[Prozess].[DB_Teilprozesse.bet-TP1]=K1)*(DB_Prozesse_2[Prozess].[DB_Teilprozesse.bet-TP2]=K1)*(DB_Prozesse_2[Prozess].[DB_Teilprozesse.bet-TP3]=K1))

      Ich hoffe, das hilft dir weiter.

      Schöne Grüße,
      Martin

      • Sebastian

        Hallo.
        Vielen Dank, das hilft weiter.
        Den Unterschied zwischen UND und ODER mit * bzw. + war mir noch gar nicht bewusst, vermutlich, weil es bisher immer nur UND-Bedingungen waren die ich benötigt habe 😉
        Danke+Gruß
        Sebastian