Neben JSON ist XML ein wichtiges Datenaustauschformat. Viele Systeme können Dateien in einem XML-Format exportieren. In diesem Tipp zeigen wir Ihnen, wie Sie eine XML-Datei per Power Query nach Excel importieren.

Ausgangsbeispiel

Die XML-Datei, die mit Hilfe von Power Query nach Excel importiert werden soll, enthält Daten von drei Kunden. Im Editor sieht die Datei folgendermaßen aus:

XML-Datei

Zu jedem Kunden sind folgende Informationen in der Datei enthalten:

• Vor- und Nachname, Ort und letzte Bestellung
• Optional ein Titel sowie eine oder mehrere Kategorien

Laden der XML-Datei in den Editor von Power Query

Um die XML-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 Aus XML.
4. Suchen Sie im Windows-Explorer nach der XML-Datei. Der Navigator von Power Query erscheint. Er sieht so aus:
   

   Navigation bei einer XML-Datei

5. Klicken Sie auf den Eintrag Kunde. Im rechten Bild des Navigators erscheint eine Vorschau auf die Daten.
6. Klicken Sie auf die Schaltfläche Daten transformieren.

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

XML-Datei im Editor von Power Query

Es werden die Daten von 3 Kunden angezeigt. In der Spalte Kategorien befinden sich bei den Kunden, denen Kategorien zugeordnet wurden, der Ausdruck Table.

Transformieren der Daten

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

Löschen des Anwendungsschritts Geänderter Typ

Beim Öffnen des Editors hat Power Query automatisch den Anwendungsschritt Geänderter  Typ eingefügt. Löschen Sie diesen Schritt, weil die Umwandlung des Datentyps in der Spalte Letzte_Bestellung nicht richtig ist.

Anzeigen der Kundendaten

1. Klicken Sie im Spaltenkopf der Spalte Kategorien auf die Schaltfläche mit den beiden Pfeilen.
2. Deaktivieren Sie in den erscheinenden Dialogbild die Option Ursprünglichen Spaltennamen als Präfix verwenden.
3. Klicken Sie auf die Schaltfläche OK:

Danach wird die Spalte Kategorien entfernt und durch die Spalte Kategorie ersetzt. Im Spaltenkopf der neuen Spalte befindet sich links nicht mehr ein Tabellensymbol, sondern der Ausdruck ABC123. Wenn einem Kunden mehr als eine Kategorie zugeordnet wurde, steht in der entsprechenden Zelle in der Spalte Kategorie der Ausdruck Table. Intern werden die Kategorien in Power Query in einer Tabelle geführt. Im nächsten Schritt soll diese Tabelle aufgelöst werden, so dass in der Zelle die Kategorien, getrennt durch ein Komma, angezeigt werden. Im Editor von Power Query sieht die Spalte so aus:

Kategorien in einer XML-Datei

Anzeigen mehrerer Kategorien in einer Zelle

1. Klicken Sie Menüband auf der Registerkarte Spalte hinzufügen auf die Schaltfläche Benutzerdefinierte Spalte. Das zugehörige Dialogbild wird angezeigt.
2. Geben Sie im Eingabefeld Neuer Spaltenname den Ausdruck Kategorie2 ein.
3. Geben Sie im Eingabefeld Benutzerdefinierte Spaltenformel folgende Formel ein:= if Value.Is([Kategorie],Table.Type) then Table.ToList([Kategorie]) else [Kategorie]

   Das Dialogbild sieht danach so aus:

   

   Neue benutzerdefinierte Spalte Kategorie2

4. Klicken Sie auf OK. Es wird eine neue Spalte mit der Überschrift Kategorie2 erzeugt. Zunächst wird in der Formel geprüft, ob in der Zelle der Ausdruck Table steht. Wenn dies der Fall ist, wird die Tabelle zunächst in eine Liste umgewandelt. Im anderen Fall wird der bisherige Eintrag in der Zelle in die neue Spalte übernommen.
5. Klicken Sie Menüband auf der Registerkarte Spalte hinzufügen erneut auf die Schaltfläche Benutzerdefinierte Spalte. Das zugehörige Dialogbild wird wieder angezeigt.
6. Geben Sie im Eingabefeld Neuer Spaltenname dieses Mal den Ausdruck Kategorie3 ein.
7. Geben Sie im Eingabefeld Benutzerdefinierte Spaltenformel folgende Formel ein:= if Value.Is([Kategorie2],List.Type) then Text.Combine(List.Transform([Kategorie2], Text.From), “,”) else [Kategorie2]

   Das Dialogbild sieht danach so aus:

   

   Neue benutzerdefinierte Spalte Kategorie3

8. Klicken Sie auf OK. Es wird eine neue Spalte mit der Überschrift Kategorie3 erzeugt. Mit der Formel wird geprüft, ob in der Zelle in der Spalte Kategorie2 der Ausdruck List steht. Wenn dies zutrifft, wird die Liste transformiert in eine Zeichenfolge, in der alle Einträge der Liste erscheinen, getrennt durch ein Komma. Im anderen Fall wird der Eintrag aus der jeweiligen Zelle in die neue Spalte Kategorie3 übernommen.

Abschließende Transformationsschritte

1. Löschen Sie die Spalten Kategorie und Kategorie2.
2. Benennen Sie die Spalte Kategorie3 in Kategorien um.
3. Ändern Sie den Datentyp der Spalte Letzte_Bestellung in Datum um.

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

Transformierte Daten aus XML-Datei mit Power Query

Anwendungsschritte

Folgende Anwendungsschritte wurden bei den Transformationen verwendet:

Ü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 XML-Datei als Ergebnistabelle in Excel

Fazit

Der Aufbau der XML-Datei bestimmt die notwendigen Transformationsschritte. In unserem Beispiel sind zwei benutzerdefinierte Spalten erzeugt worden, um die Kategorien eines Kunden in einer Zelle darstellen zu können. Solange sich der Aufbau der XML-Datei nicht ändert, können Sie den Importvorgang mit aktuellen Daten immer wiederholen.