Konvertieren Sie JSON-Array in Power BI in eine Tabelle

Angenommen, Sie haben eine JSON-Datei, die so aussieht, und Sie möchten sie als Tabelle in Power BI importieren:

{ "fields": [ { "field": "field1", "type": "string" }, { "field": "field2", "type": "string" }, { "field": "field3" , „type“: „integer“ }, { „field“: „eventTimestamp“, „type“: „date“ } ], „results“: [ [ „field1value1“, „field2value1“, 10, „26 Okt 2022 22 :01" ], [ "field1value2", "field2value2", 20, "28. Okt. 2022 15:22" ], [ "field1value3", "field2value3", 30, "17. Okt. 2022 12:46" ] ] }

Diese JSON-Datei besteht aus zwei Arrays:

• Felder;
• Ergebnis.

In der Liste "Felder“ listet die Spaltennamen auf und welche Art von Daten die Spalte enthält. Im "Ergebnis“ werden die Werte der relevanten Spalten strukturiert angezeigt.

Wir haben vier Spalten:

• Feld1;
• Feld2;
• Feld3;
• eventTimestamp.

Wir wollen den JSON als Tabelle in Power BI importieren, was zu der folgenden Tabelle führt.

Abrufen von Daten aus einer Quelle

Starten Sie Power BI und erstellen Sie einen neuen Bericht.
Wir beginnen damit, die Daten aus der JSON-Datei abzurufen. Dazu gehen wir auf die Registerkarte Anfang und klicken Sie auf die Schaltfläche Daten abrufen.

Ein neues Fenster wird geöffnet. Wenn Sie in der rechten Spalte des Fensters Nr JSON Sie sehen, geben Sie „JSON“ in das Suchfeld ein.

Der Power Query-Editor

Der Power BI Power Query-Editor wird mit der importierten JSON-Datei geöffnet. In der Tabelle sehen Sie zwei Spalten benannt Felder und Ergebnis.

Es gibt eine Zeile in der Tabelle mit a aufführen. Klicken Sie neben den Text aufführen der Spalte „Felder“ und Sie werden am unteren Rand Ihres Bildschirms (in der Vorschau) sehen, dass er vier Datensätze enthält. Dies sind die Spaltennamen.
Klicken Sie neben den Text aufführen der Spalte „Ergebnis“. Sie können sehen, dass es drei Datensätze enthält. Dies sind die Anzahl der Zeilen.

Erweitern Sie auf Zeilen

Klicken Sie in die Spalte "Ergebnis” auf die Schaltfläche mit den beiden Pfeilen klicken Auf neue Zeilen erweitern.

Die Tabelle wurde nun von einer auf drei Zeilen erweitert. Klicken Sie neben den Text aufführen der Spalte „Ergebnis“ sehen Sie die Werte der entsprechenden Zeile am unteren Rand Ihres Bildschirms (in der Vorschau).

Spalte hinzufügen

Klicken Sie auf die Registerkarte Spalte hinzufügen und klicken Sie auf die Schaltfläche Benutzerdefinierte Spalte. Ein neues Fenster wird geöffnet. Füllen Sie die folgende Formel aus:

Table.Transpose(Table.FromList([Ergebnisse], Splitter.SplitByNothing(), null, null, ExtraValues.Error))

Entfernen Sie andere Spalten

Klicken Sie mit der rechten Maustaste auf den Spaltennamen „Geändert“, das gerade erstellt wurde, und wählen Sie aus Entfernen Sie andere Spalten. Sie behalten jetzt nur noch die Spalte „Geändert" um.

Spalte auf Zeilen erweitern

Klicken Sie in die Spalte "Geändert“ auf die Schaltfläche mit den beiden Pfeilen und vergewissern Sie sich, dass die Option „Entfalten“ ausgewählt ist und klicken Sie auf „OK“.

Spaltennamen anpassen

Der nächste Schritt besteht darin, Spaltennamen hinzuzufügen, die sich in der JSON-Datei befinden. Wir tun dies, indem wir die aktuellen Tabellennamen auf die Werte in der Spalte „Felder“.

Neue Abfrage hinzufügen

Auf der rechten Seite des Power Query-Editors sehen Sie die angewendeten Schritte. Stellen Sie sich auf die dritte Stufe (Typ geändert) und klicken Sie dann mit der rechten Maustaste auf den Spaltennamen „Felder“. Wählen Sie im Kontextmenü Als neue Abfrage hinzufügen.

In Tabelle umwandeln

Gehen Sie zur Registerkarte Verwandeln und klicken Sie auf die Schaltfläche Zu Tisch in der Kategorie Umwandeln.
Es öffnet sich ein neues Fenster (Zu Tisch) und klicken Sie auf die Schaltfläche „OK“.

Auf neue Zeilen erweitern

Klicken Sie auf die Schaltfläche mit den beiden Pfeilen der Spalte und wählen Sie für Auf neue Zeilen erweitern. Nachdem sich die Zeilen erweitert haben, klicken Sie erneut auf die Schaltfläche mit den beiden Pfeilen. Drücken Sie den Knopf "OK“.

Als Ergebnis sehen wir in der ersten Spalte die Spaltennamen und in der zweiten Spalte den Datentyp.

Spaltennamen in der Wertetabelle anpassen

Wählen Sie die Tabelle mit den Daten (Beispiel) aus. Klicken Sie dann auf der Registerkarte Anfang auf der Taste Erweiterter Editor in der Kategorie Anfrage.

Es öffnet sich ein neues Fenster, in dem Sie den sogenannten M-Code einstellen können. Passen Sie den M-Code an das untenstehende Beispiel an.

 let Source = Json.Document(File.Contents("C:\Users\BjornM\OneDrive - deruitergwt.nl\Documents\Power BI\Convert JSON\sample.json")), #"Converted to table" = Table.FromRecords ({Quelle}), #"Typ geändert" = Table.TransformColumnTypes(#"In Tabelle konvertiert",{{"Felder", Typ beliebig}, {"Ergebnisse", Typ beliebig}}), #"Ergebnisse erweitert" = Table.ExpandListColumn(#"Typ geändert", "Ergebnisse"), #"Benutzerdefinierte Spalte hinzugefügt" = Table.AddColumn(#"Ergebnisse erweitert", "Benutzerdefiniert", jedes Table.Transpose(Table.FromList([Ergebnisse], Splitter .SplitByNothing(), null, null, ExtraValues.Error))), #"Andere Spalten entfernt" = Table.SelectColumns(#"Benutzerdefinierte Spalte hinzugefügt",{"Benutzerdefiniert"}), #"Benutzerdefiniert erweitert" = Table.ExpandTableColumn (#"Andere Spalten entfernt", "Benutzerdefiniert", {"Spalte1", "Spalte2", "Spalte3", "Spalte4"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", " Custom.Column4"}), #"Custom Expanded Column Names" = Table.RenameColumns(#"Custom Expanded",List.Zip({Table.ColumnNames(#"Custom Expanded"),#"fields"[Column1.field] }) ) in # „Spaltennamen anpassen“

Drücken Sie dann die Taste „OK“, um die Änderungen zu übernehmen. Als Ergebnis sehen Sie, dass alle Spaltennamen geändert wurden.

Datentyp anpassen

Der letzte Schritt besteht darin, den Datentyp jeder Spalte zu ändern. Dazu verwenden wir eine Funktion.

Funktion erstellen

Klicken Sie auf der Registerkarte Anfang an Neue Quelle und wähle eins aus Leere Abfrage.

Geben Sie den folgenden Code ein und ändern Sie den Namen der Abfrage in „ConvertType“.

= (Eingabe als Text) als Typ => let values = { {"string", type text}, {"integer", type number}, {"date", type datetimezone}, {input, type any} } , Ergebnis = List.First(List.Select(values, each _{0}=input)){1} in Result

Passen Sie den Datentyp der Spaltennamen an

In der Tabelle "Ergebnis” werden wir den letzten Schritt hinzufügen, um jeder Spalte den richtigen Datentyp zu geben.

Wählen Sie die Tabelle mit den Daten (Beispiel) aus. Klicken Sie dann auf der Registerkarte Anfang auf der Taste Erweiterter Editor in der Kategorie Anfrage.

Es öffnet sich ein neues Fenster, in dem Sie den sogenannten M-Code einstellen können. Passen Sie den M-Code an das untenstehende Beispiel an.

let Source = Json.Document(File.Contents("C:\Users\BjornM\Downloads\Convert JSON\sample.json")), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"), #"Filtered Rows" = Table.SelectRows(#"Expanded Value", every ([Name] = "results")), #"Added Custom" = Table.AddColumn(#"Gefilterte Zeilen", "Benutzerdefiniert", jeweils Table.Transpose(Table.FromList([Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error))), #"Andere Spalten entfernt " = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", " Column2", "Column3", "Column4"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4"}), #"Removed Columns" = Table.RemoveColumns(# "Expanded Custom",{"Name"}), #"Custom Rename" = Table.RenameColumns(#"Removed Columns",List.Zip({Table.ColumnNames(#"Removed Columns")),#"fields"[ field ]})), #"Custom ChangeType" = Table.TransformColumnTypes(#"Custom Rename",List.Zip({Table.ColumnNames(#"Custom Rename")),List.Transform(#"fields"[Column1. type] , jeder ConvertType(_))})) in #"Custom ChangeType"

Drücken Sie dann die Taste „OK“, um die Änderungen zu übernehmen. Als Ergebnis sehen Sie, dass die Datentypen aller Spalten angepasst wurden.

Downloads

Wenn Sie das Problem mit dem obigen Handbuch nicht lösen können, laden Sie die Beispieldatei herunter oder stellen Sie eine Frage, indem Sie eine Nachricht hinterlassen.