M Query to loop over API

Ahmad Jood 1 Reputation point
2020-12-02T10:41:23.367+00:00

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.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,787 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Ehren (MSFT) 1,781 Reputation points Microsoft Employee
    2020-12-04T19:00:53.987+00:00

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

    1 person found this answer helpful.
    0 comments No comments

  2. Ahmad Jood 1 Reputation point
    2020-12-05T14:12:05.797+00:00

    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.