access each item in a list

[Anonymous]

I am having the following M query to parse a POST request and combine the response into a data table.

let
    content = "{
    ""spec"": {
        ""ids"": [""0001"", ""0082""],
        ""interval"":""HOUR"",
        ""expressions"": [
            ""CompoundChilledWaterConsumptionForecast"",
            ""CompoundSteamConsumptionForecast"",
            ""CompoundElectricityConsumptionForecast""
        ],
        ""start"": ""2020-05-01T00:00:00.000"",
        ""end"": ""2020-05-12T09:00:00.000"",
        ""include"": ""start, end, interval, count, dates, data, timeZone, unit""
        }
    }",
    Source = Json.Document(Web.Contents("https://engie-osep.c3iot.com/api/1/engie-osep/prod/Facility?action=evalMetrics", [Headers=[#"Authorization"="Basic xxxx", #"Content-Type"="application/json", #"Accept"="application/json"], Content=Text.ToBinary(content)])),
    result = Source[result],
    keys = Record.ToTable(result)[Name],
    // table = Table.FromRows({},{"DateTime","Steam","Chilled Water", "Electricity", "Id"}),
    tables = List.Generate(
        () => [i=-1, table = #table({},{})],
        each [i] < List.Count(keys),
        each [
            i=[i]+1,
            table = Table.AddColumn(Table.FromColumns({result[keys{i}][CompoundChilledWaterConsumptionForecast][dates], result[keys{i}][CompoundChilledWaterConsumptionForecast][data], result[keys{i}][CompoundSteamConsumptionForecast][data], result[keys{i}][CompoundElectricityConsumptionForecast][data]}, {"DateTime", "ChilledWater", "Steam", "Electricity"}), "TableName", each keys{i})
        ],
        each [table]
    ),
    table2 = Table.Combine(tables)
in
    table2

I am having an identifier error on this part:

table = Table.AddColumn(Table.FromColumns({result[keys{i}][CompoundChilledWaterConsumptionForecast][dates], result[keys{i}][CompoundChilledWaterConsumptionForecast][data], result[keys{i}][CompoundSteamConsumptionForecast][data], result[keys{i}][CompoundElectricityConsumptionForecast][data]}, {"DateTime", "ChilledWater", "Steam", "Electricity"}), "TableName", each keys{i})

because 

result[keys{i}]

 is considered invalid (however, if I just put individual keys in it, such as result[0001], the query executes fines). My question is how do I access different records using keys{i}. Any help is appreciated.

[edhans]

Use either of the following:

result[keys]{i}
result{i}[keys]

The former is the column, then then index number of the row, the latter is the row, then the column.

Don't nest the brackets like you've done.

Ping back if that helps. 

[Anonymous]

Thanks a lot for the help. I tried both 'result[keys]{i}' and 'result{i}[keys]', unfortunately it is still throwing me error messages. Here is what my 'result' looks like

and my 'keys' is a list:

it seems to me like no matter what, I will need to access individual item in the keys list with 'keys{i}' before I can access individual records in 'result'. 

[AlB]

Hi @Anonymous 

Try  replacing result[keys{i}] by this:

Expression.Evaluate("result[" & keys{i} & "]", [result = result, keys = keys, i = i ] )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[edhans]

not sure exactly what the error is. Those records and lists are what you see. What are you expecting? What is the error you see?

And perhaps we take this from a different approach. Why are you trying to access specific "cells" in the table? THere may be a different way to accomplish your goal if this datasource isn't supporting this properly.

[Anonymous]

so the data I got from a POST response is a JSON file that contains keys and values like this:

{

    'key1': {'dates': {x,x,x}, 'value1': {x,x,x}, 'value2': {x,x,x}, 'value3': {x,x,x}},

    'key2': {'dates': {x,x,x}, 'value1': {x,x,x}, 'value2': {x,x,x}, 'value3': {x,x,x}}

}

what I am trying to do is to format the above JSON file and make it a single data table which contains the following columns:

dates, value1, value2, value3, key_identifier

To do this, I suppose I have to loop over the JSON keys and extract the values associated with the keys. Being a Power BI newbie, that is the only solution I can think of.

[edhans]

Yes. That seems reasonable. I'd need the JSON file to play with though to see if there were other alternatives. I'm not going to copy and paste data and try to recreate a JSON file. I'd never get it right.

[Greg_Deckler]

@ImkeF @edhans