Forum Discussion
Convert a value of type Record to type Text
Dears,
I have connect to my API to retrieve data and each request give me 50 in a page so i built this but for a reason it give me an error
Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=[Record]
Type=[Type]
and when I check the error it gives me on this part:
GetJson = (Url) =>
let
RawData = Web.Contents(Url/*, Options*/),
Json = Json.Document(RawData)
in
Json,
the out put should be like this:
this is my code:
let
BaseUrl = Json.Document(Web.Contents("https://api.XXXXXXXXXXXX\orders.com",
[
Headers=
[
Authorization="Bearer XXXXXXXXXXXX",
Accept="application/json",
#"Content-Type"="application/json"
]
] )
),
EntitiesPerPage = 50,
Url = BaseUrl,
GetJson = (Url) =>
let
RawData = Web.Contents(Url/*, Options*/),
Json = Json.Document(RawData)
in
Json,
GetTotalCount = () =>
let
Json = GetJson(Url),
Entities = Json[Count]
in
Entities,
EntityCount = GetTotalCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndex = { 1 .. PageCount},
GetPage = (PageIndex) =>
let
PageUrl = BaseUrl & "page=" & Text.From(PageIndex),
Json = GetJson(PageUrl),
Value = Json[orders]
in
Value,
GetUrl = (PageIndex) =>
let
PageNum = "page=" & Text.From(PageIndex),
PageUrl = BaseUrl & PageNum
in
PageUrl,
Urls = List.Transform(PageIndex, each GetUrl(_)),
Pages = List.Transform(PageIndex, each GetPage(_)),
DataList = List.Union(Pages),
TableFromList = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"TableFromList"
Any advice please?
In case it helps, FYI that I just put out a video on using REST APIs with power query. It shows how to get the count and use it to create a list of numbers to make multiple calls with $skip or offset to get all the data (so don't have to worry about pagination).
Power BI - Tales From The Front - REST APIs - YouTube
Regards,
Pat
11 Replies
- ImkeF
Community Champion
Hi Anonymous ,
I'm having problems understanding your problem description.
But I can see what's probably causing trouble:
You're using the "URL"-step/variable in the "GetJson"-function in the "Raw data"-step.
For it to work there, it has to be in text format.But the value you're assigning this variable with ("BaseURL") is most likely a record.
That would explain the error message.
So what you would have to do there is to parse out the actual URL string before assigning it to the variable "URL".
Dear ImkeF thank you for reply, basically i'm loping over my API to get all pages and store it in one table but the issue that it give an error "We cannot convert a value of type Record to type Text" .
When i'm calling one page i'm using this and it working perfect:
let Source = Json.Document(Web.Contents("https://api.xxxxx/orders/orders", [ Headers= [ Authorization="Bearer XXXXXXXXXX", Accept="application/json", #"Content-Type"="application/json" ] ] ) ) in Sourceand this is the output
but as i mention above for the error is come, i will try to split the URL and try,
ImkeF could you please advice in how can I parse the URL to converte it from record to list?
Thank you in advance
- mahoneypat
Microsoft Employee
In case it helps, FYI that I just put out a video on using REST APIs with power query. It shows how to get the count and use it to create a list of numbers to make multiple calls with $skip or offset to get all the data (so don't have to worry about pagination).
Power BI - Tales From The Front - REST APIs - YouTube
Regards,
Pat
Dear mahoneypat thank you for your reply and usefully post, but in my case i will not search for a value so i will not enter a value, the value will be my module like "Student, Orders, Clasess" so if i call one page then invoke function to get all pages that i want to loop over, right?
if not how i will applay the same logic for my first API call to loop over?
mahoneypat I have revise my query i was missing a few things and now all is good and I'm using your logic, but i have one issue, that in my API i have 861 Pages and it only load the first 460 page which is the half.
And onle more thing and I think you can help me in that, i'm trying to apply a delay duration to delay the API call by 2 or 3 seconds so do you think it is possible, as i tried to apply that but not working.
- Nathaniel_C
Hi Anonymous ,
Let's see if ImkeF is available...
Nathaniel - mahoneypat
In your original query, what do you see when you click on List in the data field of the record? Does that contain your "Student, Orders, Classes" data?
Regards,
Pat
when i click on list then transform it to table it takes me to list with 50 records then i convert that list again to table after that expanding the column inside that column i have "Student, Orders, Classes, ID, Full Name, Grad, Location, Mobile, Email".
This is what i get if i generate only one request for one page, and i have like 300 pages.
mahoneypat i tried the same way but It loop over the first page only and duplicate the records.
- mahoneypat
Glad to hear you are making progress. This article explains how to use the Function.InvokeAfter function to add a delay between calls.
Regards,
Pat
