The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have been struggling with an API issue of getting multiple pages of records in one query for some days now. Originally posted the issue in this forum, got no responses, posted it on Reddit, and got a response with a person that helped me with this adaptation of the code below. I feel I am much further along, but now I am getting the "Expression.Error: We cannot convert a value of type Record to type List." error.
Maybe somebody here has run into this problem and will be able to help. Thanks in advance!
let
GetPage = (offset) =>
let
body = "
{
""id"":""123"",
""method"":""getLeads"",
""params"":{
""where"":{
},
""limit"":""500"",
""offset"":"" & Text.From(offset) & ""
}
}",
Data = Json.Document(
Web.Contents(
"https://api.sharpspring.com/pubapi/v1.2/?accountID=##########&secretKey=##########",
[
Content = Text.ToBinary(body),
Headers=[#"Content-Type"="application/json"]
]
)
)
in
Data,
GetPages = (offset as number, accumulatedData as list) =>
let
currentPageData = GetPage(offset),
newData = List.Combine({accumulatedData, currentPageData}),
nextOffset = offset + 500
in
if List.Count(currentPageData) = 500 then @GetPages(nextOffset, newData) else newData,
Result = GetPages(0, {})
in
Result
Ok maybe this will help somebody understand better because I still can't wrap my head around what to do to fix it.
If I change the line:
currentPageData = GetPage(offset),
to
currentPageData = Record.ToList(GetPage(offset)),
Then I am able to get this as "result":
I have to dive into that record to get a step "result1" to get this:
Then inside that is my potential list of 500 records. So in the code when it is looking for 500 records or not, it is only at the step where it is looking at 5 records in the picture above. Which would obviously result it it not trying to grab any more pages.. I think. I could be wrong about everything i'm thinking here. I've tried to wrap my head around how to move that part where its checking for 500 records or not after the couple drill down steps, but nothing has worked so far.
Most likely CurrentPageData is not a list yet. You need to navigate through it to identify the property that has the data rows.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
85 | |
75 | |
55 | |
46 |
User | Count |
---|---|
134 | |
124 | |
78 | |
64 | |
63 |