Forum Discussion
Custom API call added as column
Hi guys
Hope you can help me out. I have got a column with a logger ID for each row. Then added a custom column with API web to retrieve data for each loggerid. Then I simply added another column1 with Column 1 = Json.Document(Web.Contents([Custom])). This works perfectly and its bringing data for each one. Due to the page size limit on API, its only making only one call. I have found the code which works perfectly to retrieve data through multiple pages on a set page number defined for a single statuc ID.
List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
each List.Count(Record.FieldNames([WebCall]))<0 or [Counter]<10, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://energycloud.com/api/LoggerData/dailytotals?&channelNumber=1&apiKey=c&pageSize=100&orderby=id&page="&Text.From([Page])&"")), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1)
When I added as another custom column for each row to bring in data for each row (ID) for multiple pages using the above code power bi coming up with an error saying "Custom" column record cannot be found. I know I am almost there but its just I dont know where to go after this blocker?
List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
each List.Count(Record.FieldNames([WebCall]))<0 or [Counter]<10, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents([Custom]="&Text.From([Page])&")), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1)
Expression.Error: The field 'Custom' of the record wasn't found.
Details:
WebCall=
Page=1
Counter=0
Any help would be greatly appreciated!
Thanks in advance.
6 Replies
- mariopavicFrequent Visitor
Are you sure that "Custom" column exists? Also, why do you have "=" after "[Custom]" in your code?
- SankzpowerHelper I
mariopavic Yes, the custom column exists as below. The custom column contains the API web address as https://energycloud.com/api/LoggerData/dailytotals?&channelNumber=1&apiKey=cb&pageSize=100&orderby=id&page
The equal symbol equals to page number
- mariopavicFrequent Visitor
[Custom]&"="&Text.From([Page])Try this