Convert a value of type Record to type Text

[Anonymous]

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? 

@MarcelB  

[mahoneypat]

Glad to hear you are making progress.  This article explains how to use the Function.InvokeAfter function to add a delay between calls.

Using the Power Function.InvokeAfter to determine how long to wait between API calls - Reporting/Ana...

Regards,

Pat

[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

[Anonymous]

@mahoneypat  i tried the same way but It loop over the first page only and duplicate the records.

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

@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. 

[Anonymous]

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?

[ImkeF]

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".

[Anonymous]

@ImkeF could you please advice in how can I parse the URL to converte it from record to list?

Thank you in advance 

[Anonymous]

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
    Source

 and this is the output 

but as i mention above for the error is come, i will try to split the URL and try, 

[Nathaniel_C]

Hi @Anonymous ,

Let's see if @ImkeF is available...
Nathaniel