JSON array converteren naar tabel in Power BI

Stel je hebt een JSON bestand welke er als volgt uitziet en als tabel willen importeren in Power BI:

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

Dit JSON-bestand bestaat uit twee arrays:

• fields;
• results.

In de lijst “fields” staan de kolomnamen en wat voor gegevenstype de kolom bevat. In “results” staan de waarden van de betreffende kolommen gestructureerd weergegeven.

We hebben vier kolommen:

• field1;
• field2;
• field3;
• eventTimestamp.

We willen de JSON importeren in Power BI als tabelvorm met als resultaat onderstaande tabel.

Gegevens ophalen uit een bron

Start Power BI op en maak een nieuw rapport aan.
We beginnen met ophalen van de gegevens uit het JSON-bestand. Hiervoor gaan we naar het tabblad Start en klikken op de knop Gegevens ophalen.

Er opent zich een nieuw venster. Indien je In de rechterkolom van het venster geen JSON ziet staan vul je in het zoekvenster “JSON” in.

De Power Query-editor

De Power Query-editor van Power BI wordt geopend met het geïmporteerde JSON-bestand. In de tabel zie je twee kolommen genaamd fields en results.

In de tabel bevindt zich één rij met een List. Klik naast de tekst List van de kolom “fields” en je ziet onderin je scherm (in de preview) dat deze vier records bevat. Dit zijn de kolomnamen.
Klik naast de tekst List van de kolom “results“. Je ziet dat deze drie records bevat. Dit zijn het aantal rijen.

Uitvouwen naar rijen

Klik in de kolom “results” op de knop met de twee pijltjes klik op Uitvouwen naar nieuwe rijen.

De tabel is nu uitgebreid van één naar drie rijen. Klik naast de tekst List van de kolom “results” je ziet onderin je scherm (in de preview) de waarden van de betreffende rij.

Kolom toevoegen

Klik op het tabblad Kolom toevoegen en klik op de knop Aangepaste kolom. Er opent zich een nieuw venster. Vul hierin de volgende formule:

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

Andere kolommen verwijderen

Klik met de rechtermuisknop op de kolomnaam “Aangepast” welke net is aangemaakt en kies voor Andere kolommen verwijderen. Je houdt nu alleen de kolom “Aangepast” over.

Kolom uitvouwen naar rijen

Klik in de kolom “Aangepast” op de knop met de twee pijltjes en zorg ervoor dat de optie “Uitvouwen” is geselecteerd en klik op de knop “OK“.

Kolomnamen aanpassen

De volgende stap is om kolomnamen welke in het JSON-bestand staan toe te voegen. Dit doen we door de huidige tabelnamen aan te passen naar de waarden in de kolom “fields“.

Toevoegen nieuwe Query

Aan de rechterkant van de Power Query-editor zie je de toegepaste stappen staan. Ga op de derde stap staan (Type gewijzigd) en klik vervolgens met je rechtermuis op de kolomnaam “fields“. Vanuit het contextmenu kies je voor Toevoegen als nieuwe query.

Converteren naar tabel

Ga naar het tabblad Transformeren en klik op de knop Naar tabel in de categorie Converteren.
Er opent zich een nieuw venster (Naar tabel) en klik hier op de knop “OK“.

Uitvouwen naar nieuwe rijen

Klik op de knop met de twee pijltjes van de kolom en kies voor Uitvouwen naar nieuwe rijen. Nadat de rijen zijn uitgevouwen klik je nogmaals op de knop met de twee pijltjes. Klik op de knop “OK“.

Als resultaat zien we in de eerste kolom staan de kolomnamen en in de tweede kolom het gegevenstype.

Kolomnamen aanpassen in tabel met waarden

Selecteer de tabel met de gegevens (sample). Klik vervolgens vanuit het tabblad Start op de knop Geavanceerde editor in de categorie Query.

Er opent zich een nieuw venster waarin je de zogeheten M-code kunt aanpassen. Pas de M-code aan naar onderstaand voorbeeld.

 let
    Bron = Json.Document(File.Contents("C:\Users\BjornM\OneDrive - deruitergwt.nl\Documenten\Power BI\Convert JSON\sample.json")),
    #"Geconverteerd naar tabel" = Table.FromRecords({Bron}),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Geconverteerd naar tabel",{{"fields", type any}, {"results", type any}}),
    #"results uitgevouwen" = Table.ExpandListColumn(#"Type gewijzigd", "results"),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"results uitgevouwen", "Aangepast", each Table.Transpose(Table.FromList([results], Splitter.SplitByNothing(), null, null, ExtraValues.Error))),
    #"Andere kolommen verwijderd" = Table.SelectColumns(#"Aangepaste kolom toegevoegd",{"Aangepast"}),
    #"Aangepast uitgevouwen" = Table.ExpandTableColumn(#"Andere kolommen verwijderd", "Aangepast", {"Column1", "Column2", "Column3", "Column4"}, {"Aangepast.Column1", "Aangepast.Column2", "Aangepast.Column3", "Aangepast.Column4"}),
    #"Kolomnamen aanpassen" = Table.RenameColumns(#"Aangepast uitgevouwen",List.Zip({Table.ColumnNames(#"Aangepast uitgevouwen"),#"fields"[Column1.field]}))
in
    #"Kolomnamen aanpassen"

Druk vervolgens op de knop “OK” om de wijzigingen door te voeren. Als resultaat zie je dat alle kolomnamen zijn aangepast.

Gegevenstype aanpassen

Als laatste stap dient het gegevenstype van elke kolom te worden aangepast. Hiervoor maken we gebruik van een functie.

Functie maken

Klik vanuit de tab Start op Nieuwe bron en kies voor een Lege query.

Voor onderstaande code in en pas de naam van de query aan naar “ConvertType“.

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

Gegevenstype aanpassen van de kolomnamen

In de tabel “results” gaan we de laatste stap toevoegen om iedere kolom het juiste gegevenstype te geven.

Selecteer de tabel met de gegevens (sample). Klik vervolgens vanuit het tabblad Start op de knop Geavanceerde editor in de categorie Query.

Er opent zich een nieuw venster waarin je de zogeheten M-code kunt aanpassen. Pas de M-code aan naar onderstaand voorbeeld.

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", each ([Name] = "results")),
     #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.Transpose(Table.FromList([Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error))),
    #"Removed Other Columns" = 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], each ConvertType(_))}))
in
    #"Custom ChangeType"

Druk vervolgens op de knop “OK” om de wijzigingen door te voeren. Als resultaat zie je dat van alle kolommen de gegevenstypen zijn aangepast.

Downloads

Kom je er met bovenstaande handleiding alleen niet uit, download dan het voorbeeldbestand of stel een vraag door een bericht achter te laten.