How to convert data into a table in Power BI after integrating an API with JSON data?

Question

I would like some support in converting data obtained from an API in JSON to a table in Power BI's Power Query.

When converting to a table I notice that column 1 has the IDs but the next 3 columns have a "data list" in each line.

When expanding the new column I see that the ID number is repeated several times and that is OK so far

The problem is when I expand the next columns, the data is not referenced by row. The wind_speed and power data are being repeated, but on different dates.

For each ID we have a different DATA, WIND_SPEED and POWER data set. In JSON I can see that within each ID the data is separated correctly. My question has been about converting the data into Power Query in Power BI.

Below is the complete query code in Power Query:

let
    // Definir os dados JSON
    json = [
        api_token = "cb8551da59dbdf91be8371df4a03dddb",
        power_source = "wind",
        id = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40},
        variables = {"wind_speed", "power"},
        end_date = "20240601",
        integration = "daily"
    ],

    // Converter o JSON em uma string JSON formatada
    jsonString = Text.FromBinary(Json.FromValue(json)),

    // Definir a URL
    url = "https://api.tempook.com/geracao/v1/forecast/multiforecast/range/integration",

    // Obter os dados da API usando o método POST
    response = Web.Contents(
        url,
        [
            Headers = [
                #"Content-Type"="application/json"
            ],
            Content = Text.ToBinary(jsonString),
            ManualStatusHandling={405}
        ]
    ),

    // Converter a resposta JSON em uma tabela
    jsonResponse = Json.Document(response),
    #"Convertido para Tabela" = Record.ToTable(jsonResponse),
    Value = #"Convertido para Tabela"{0}[Value],
    #"Convertido para Tabela1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 Expandido" = Table.ExpandRecordColumn(#"Convertido para Tabela1", "Column1", {"id", "power", "wind_speed", "times"}, {"Column1.id", "Column1.power", "Column1.wind_speed", "Column1.times"}),
    #"Column1.power Expandido" = Table.ExpandListColumn(#"Column1 Expandido", "Column1.power"),
    #"Column1.wind_speed Expandido" = Table.ExpandListColumn(#"Column1.power Expandido", "Column1.wind_speed"),
    #"Column1.times Expandido" = Table.ExpandListColumn(#"Column1.wind_speed Expandido", "Column1.times")
in
    #"Column1.times Expandido"

I tried using Power Query's own function in Power BI to convert the data to a table, but something seems to get lost when this is done.

Answer 1

Try combining those data columns into a table and then expanding

let
   [...]
    // Converter a resposta JSON em uma tabela
    jsonResponse = Json.Document(response),
    #"Convertido para Tabela" = Record.ToTable(jsonResponse),
    Value = Table.FromRecords(List.Transform(#"Convertido para Tabela"{0}[Value], each [id = [id], data = Table.FromColumns({[power], [wind_speed], [times]}, {"power", "wind_speed", "times"})])),
    #"Expanded data" = Table.ExpandTableColumn(Value, "data", {"power", "wind_speed", "times"}, {"power", "wind_speed", "times"})
in
    #"Expanded data"