Forum Discussion
access each item in a list
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.
7 Replies
- Greg_Deckler
Community Champion
- 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.
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'.
- 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.
