M Query to loop over API

Question

Dears,

I have built M Query to loop over my API and get all pages that I have then store them in a table so I get the related data and extract them, the issue is on certain point I got an error:

**Expression.Error: We cannot convert a value of type Record to type Text.
Details:
    Value=[Record]
    Type=[Type]**

For this part of my query:

**(Url) =>

  let

    RawData = Web.Contents(Url/*, Options*/),

      Json = Json.Document(RawData)

in

  Json**

And I tried to solve it and change the logic with no luck, could you please help me in that? This is the code that I'm using:

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"

Thank you so much.

Answer

If the error is indeed happening in GetJson as you suggested, then it seems likely that GetJson is being passed a record value for the Url instead of a text value.

EDIT: Yes, that appears to be the issue. Your code is treating BaseUrl as a text value, but it's actually a record (the result of Json.Document).

EDIT: Try changing the definition of BaseUrl to just the url text value.
BaseUrl = "https://api.XXXXXXXXXXXX\orders.com",

Answer

Thank you so much @Ehren (MSFT) for your response, so how can I solve such an issue, to be honest, I'm new to M query and I don't know how and what function should be used, I'm not aware of all M query functions.

Can you please support me in this.

M Query to loop over API

2 answers