JSON als Datenaustauschformat spielt eine immer größere Rolle. Viele Webservices stellen Ihre Daten im JSON-Format zur Verfügung. In diesem Tipp zeigen wir Ihnen, wie Sie eine Textdatei im JSON-Format per Power Query nach Excel importieren.

Ausgangsbeispiel

Die JSON-Datei, die mit Hilfe von Power Query nach Excel importiert werden soll, enthält Daten von 3 Kontaktpersonen. Die Datei sieht im Editor folgendermaßen aus:

JSON-Datei

Das erste Zeichen in der JSON-Datei ist eine sich öffnende eckige Klammer. Sie kennzeichnet den Beginn eines Arrays oder Datenfeldes. Danach folgen in geschweiften Klammern die Daten der einzelnen Kontaktpersonen. Die Bezeichnungen werden in Anführungszeichen gesetzt. Nach der Bezeichnung folgt ein Doppelpunkt. Text- und Datumsinformationen werden in Anführungszeichen gesetzt. Dezimalzahlen mit Nachkommastellen enthalten einen Dezimalpunkt. Einer Kontaktperson können eine oder mehrere Kategorien zugeordnet werden. Diese Kategorien werden in einem Array mit einer sich öffnenden und schließenden eckigen Klammer dargestellt. Am Ende der Datei steht eine schließende, eckige Klammer.

Laden der JSON-Datei in den Editor von Power Query

Um die JSON-Datei in Power Query zu laden, gehen Sie folgendermaßen vor:

1. Legen Sie in Excel eine neue Arbeitsmappe an.
2. Klicken Sie im Menüband auf der Registerkarte Daten in der Gruppe Daten abrufen und transformieren auf die Schaltfläche Daten abrufen.
3. Klicken Sie danach auf den Menüpunkt Aus Datei und dann auf Von JSON.
4. Suchen Sie im Windows-Explorer nach der JSON-Datei.

Danach wird der Editor von Power Query gestartet. Er sieht in unserem Beispiel so aus:

JSON-Datei als Liste mit Records

Die Daten der 3 Kontaktpersonen aus der JSON-Datei erscheinen als Liste mit 3 Records. Ein einzelner Record sieht in der JSON-Datei so aus:

JSON-Datei mit einzelnem Record

Transformieren der Daten

Bevor die Daten nach Excel übertragen werden, sind einige Transformationsschritte notwendig.

Umwandeln der Liste von Records in eine Tabelle von Records

1. Klicken Sie in den Listentools von Power Query auf die Schaltfläche Zu Tabelle. Es öffnet sich das Dialogbild Zu Tabelle.
2. Sie brauchen keine Änderungen vorzunehmen. Klicken Sie auf OK.

Die neu erzeugte Tabelle wird so dargestellt:

JSON-Datei als Tabelle mit Records

Erweitern der Spalte in der Tabelle

1. Klicken Sie im Spaltenkopf der Spalte Column1 auf die Schaltfläche mit den beiden Pfeilen.
2. In dem sich öffnenden Dialogbild deaktivieren Sie die Option Ursprünglichen Spaltennamen als Präfix verwenden.
3. Klicken Sie auf OK.

Die einzelnen Informationen zu den Kontaktpersonen werden in verschiedenen Spalten dargestellt. In der Spalte Kategorien erscheint entweder der Eintrag null, wenn der Kontaktperson keine Kategorie zugeordnet wurde, oder der Eintrag List.

Kategorien in einer JSON-Datei

Bezeichnungen der Kategorien sichtbar machen

Um die Kategorien sichtbar zu machen, gehen Sie folgendermaßen vor:

1. Klicken Sie im Menüband auf der Registerkarte Spalte hinzufügen auf die Schaltfläche Neue benutzerdefinierte Spalte. Das zugehörige Dialogbild wird angezeigt.
2. Geben Sie im Eingabefeld Neuer Spaltenname den Ausdruck Kategorien_neu ein.
3. Geben Sie im Eingabefeld Benutzerdefinierte Spaltenformel folgende Formel ein:
   = if [Kategorien] = null then {} else List.Transform([Kategorien], each Record.Field(_, “Bezeichnung”)). Das Dialogbild sieht danach so aus:
   

   Neue benutzerdefinierte Spalte

4. Klicken Sie auf OK. Es wird eine neue Spalte mit der Überschrift Kategorien_Neu erzeugt.

Zunächst wird in der Formel geprüft, ob einer Person eine Kategorie zugeordnet wurde. Wenn es keine Kategorie gibt, d. h. in der Spalte erscheint der Wert null, wird eine leere Liste erzeugt. Die leere Liste erzeugen Sie mit dem Ausdruck {}. Im anderen Fall wird in der Spalte der Ausdruck List angezeigt, d. h. die Kategorien werden zurzeit als Liste von Records dargestellt. Die Liste enthält so viele Records wie es zugeordnete Kategorien gibt. Diese Liste von Records wird mit der Funktion List.Transform in eine andere Liste umgewandelt. Aus jedem Record der Liste wird der Inhalt des Feldes Bezeichnung herausgezogen und in eine neue Liste übertragen. So wird pro Kontaktperson eine Liste von Kategorien erzeugt.

Im nächsten Schritt müssen die Werte aus dieser neuen Liste extrahiert werden:

1. Klicken Sie im Spaltenkopf der Spalte Kategorien_Neu auf die Schaltfläche mit den beiden Pfeilen.
2. Klicken Sie auf den Menüpunkt Werte extrahieren. Das zugehörige Dialogbild erscheint.
3. Wählen Sie als Trennzeichen für das Verketten von Listenwerten den Eintrag Komma.
4. Klicken Sie auf OK.

Abschließende Transformationsschritte

1. Löschen Sie die Spalte Kategorien.
2. Benennen Sie die Spalte Kategorien_neu in Kategorien um.
3. Ändern Sie die Datentypen der Spalten Größe und Gewicht in Dezimalzahl, der Spalte Lieferdatum in Datum und der übrigen Spalten in Text um.

Danach sehen die Daten im Editor von Power Query so aus:

Transformierte Daten aus JSON-Datei mit Power Query

Übertragen der Daten nach Excel

Wenn Sie im Menüband auf der Registerkarte Start auf die Schaltfläche Schließen und Laden klicken, werden die Daten in ein neues Excel-Tabellenblatt übertragen. Die Tabelle sieht in Excel so aus:

Importierte JSON-Datei als Ergebnistabelle in Excel

Fazit

Der Aufbau der JSON-Datei bestimmt die notwendigen Transformationsschritte. In unserem Beispiel ist eine neue benutzerdefinierte Spalte erzeugt worden, um die Kategorien einer Kontaktperson in einer Zelle darstellen zu können. Solange sich der Aufbau der JSON-Datei nicht ändert, können Sie den Importvorgang mit aktuellen Daten immer wiederholen.