Some data sources may not be listed because of hand-authored queries

[Shamatix]

Hi fellow users of Power Bi,

I am sitting here trying to use M to gather data from an API.

I have finally succesfully managed to dynamically get an API key.

After trying to gather my data from the API I noticed I only got 100 "entries" and not all of em, so I searched around and found "Pagination" and managed to loop through all the records.

However I get hit by an error:
"We couldn't authenticate with the credentials provided. Please try again"

And I noticed the following:
"Some data sources may not be listed because of hand-authored queries"

I am using a link as such:

https://api.XXXXXX.dk/v1/organizations/XXXXXX/vouchers?count=100

The above link changes the count=100 to count=200 and so on untill we have gathered em all.

But how can I get around the "Some data sources may not be listed because of hand-authored queries" as the link is somewhat "Dynamic"?

Best regards

[v-lid-msft]

Hi @Shamatix ,

Do you mean generate API Key inside the M Query when you mentioned "dynamically get an API key"? Can you get data from constant url such as "count=100" without loop? Please also refer to the similar thread use loop to get all data : https://community.powerbi.com/t5/Desktop/Trouble-with-Pagnation-for-Airtable/m-p/838776#M402836

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shamatix]

Thanks for your answer.

I actually managed to find the issue causing it.

However now I stumbled upon a new issue 

"2 arguments were passed to a function which expects 1."

I do not understand how or why it's getting two arguements, hopefully you can help 🙂 

Below is the M code I am trying to run.

What I am trying to do is first getting an authentication token (this part works) and then I want to loop through all the "pages" so to speak, using an "offset"

let
// Get authorization token
Kilde = Web.Contents("https://api.XXXXXXXXX.dk/oauth2/token",
[Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(Uri.BuildQueryString([grant_type="password", client_id="XXXXXXXXXXXXXXXXX", client_secret="XXXXXXXXXXXXXX", email="xxxxxxx@yyyyyyyyy.com", password="XXXXXXXXXXXXX"]))]),
KildeTekst = Text.FromBinary(Kilde),
KildeSegmenter = Splitter.SplitTextByDelimiter("&")(KildeTekst),
KildeKvps = List.Transform( KildeSegmenter,
(value) => {
List.First((Splitter.SplitTextByDelimiter("=")(value))),
List.Last((Splitter.SplitTextByDelimiter("=")(value)))
}
),
KildeKvpTabel = Table.FromList(KildeSegmenter, Splitter.SplitTextByDelimiter("=")),
#"Filtrerede rækker" = Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"}),
Token = Text.From(List.First(Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"})[Column2])),

EntitiesPerPage = 100,
BaseUrl = "https://api.xxxxxxxxxxx.dk/v1/organizations/XXXX/vouchers",

GetJson = (Url) =>
let Options = [Headers =[#"Authorization"="Bearer " & Token]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData,1252)
in Json,

GetEntityCount = () =>
let Url = BaseUrl & "?count=10",
Options = [Headers =[#"Authorization"="Bearer " & Token]],
Json = Web.Contents(Url, Options),
#"Derp" = Json.Document(Json,1252),
Count = Record.Field(#"Derp"[meta],"count")
in Count,

GetPage = (Index) =>
let
Options = [Headers =[#"Authorization"="Bearer " & Token]],
Skip = "&offset=" & Text.From(Index * EntitiesPerPage),
Top = "?count=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Top & Skip,
Json = GetJson(Url, Options),
Value = Json[#"value"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
Table

[v-lid-msft]

Hi @Shamatix ,

Advanced Editor shows "No syntax errors have been detected" on myside, have you tried comment each step from the back to the front to find which step occor this error?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shamatix]

Hi again,

No exactly, there isn't any syntax errors

However I am rather sure it's this line:
Pages = List.Transform(PageIndices, each GetPage(_)),

And I am pretty sure it's the "GetPage(_)"

GetPage = (Index) =>
let
Options = [Headers =[#"Authorization"="Bearer " & Token]],
Skip = "&offset=" & Text.From(Index * EntitiesPerPage),
Top = "?count=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Top & Skip,
Json = GetJson(Url, Options),
Value = Json[#"value"]
in Value,

I think what's actually causing the error is:

It returns me a "Data" list AND a "Meta" list while I only expected the Data?
How can I make GetPage = (Index) => only contain the data list?

That causes the error

[Shamatix]

Furthermore I think it's cus the API returns a META and a DATA row while I expect only a DATA row.

How can I do so Value = Json[#"value"] only looks at "Data" and not "META"?

Best regards

[v-lid-msft]

Hi @Shamatix ,

We tried to connect a local json and get the list after appending [data] in the end.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shamatix]

Sadly this did not help.

GetPage = (Index) =>
let
Options = [Headers =[#"Authorization"="Bearer " & Token]],
Skip = "&offset=" & Text.From(Index * EntitiesPerPage),
Top = "?count=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Top & Skip,
Json = GetJson(Url, Options)[data],
Value = Json[#"value"][data]
in Value,

And

GetJson = (Url) =>
let Options = [Headers =[#"Authorization"="Bearer " & Token]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData,1252)[data]
in Json,

As you can see I tried to include [Data] multiple places, without any luck 😞

[v-lid-msft]

Hi @Shamatix ,

From your previous api screenshot, the GetJson function will return a json document with following document:

{
  "data": [],
  "meta": {}
}

so we can use [data] after the result of GetJson function to get a list value. We try to change your query as following:

let
// Get authorization token
Kilde = Web.Contents("https://api.XXXXXXXXX.dk/oauth2/token",
[Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(Uri.BuildQueryString([grant_type="password", client_id="XXXXXXXXXXXXXXXXX", client_secret="XXXXXXXXXXXXXX", email="xxxxxxx@yyyyyyyyy.com", password="XXXXXXXXXXXXX"]))]),
KildeTekst = Text.FromBinary(Kilde),
KildeSegmenter = Splitter.SplitTextByDelimiter("&")(KildeTekst),
KildeKvps = List.Transform( KildeSegmenter,
(value) => {
List.First((Splitter.SplitTextByDelimiter("=")(value))),
List.Last((Splitter.SplitTextByDelimiter("=")(value)))
}
),
KildeKvpTabel = Table.FromList(KildeSegmenter, Splitter.SplitTextByDelimiter("=")),
#"Filtrerede rækker" = Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"}),
Token = Text.From(List.First(Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"})[Column2])),


EntitiesPerPage = 100,
BaseUrl = "https://api.xxxxxxxxxxx.dk/v1/organizations/XXXX/vouchers",


GetJson = (Url) =>
let Options = [Headers =[#"Authorization"="Bearer " & Token]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData,1252)
in Json,


GetEntityCount = () =>
let Url = BaseUrl & "?count=10",
Options = [Headers =[#"Authorization"="Bearer " & Token]],
Json = Web.Contents(Url, Options),
#"Derp" = Json.Document(Json,1252),
Count = Record.Field(#"Derp"[meta],"count")
in Count,


GetPage = (Index) =>
let
Options = [Headers =[#"Authorization"="Bearer " & Token]],
Skip = "&offset=" & Text.From(Index * EntitiesPerPage),
Top = "?count=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Top & Skip,
Json = GetJson(Url, Options),
Value = Json[data][#"value"]
in Value,


EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
Table

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shamatix]

Hi there,

We have now gotten a bit further and is now stumbling upon a new error:
"We cannot apply field access to the type List."

Below is our M code

let
    // Få authorization token
    Kilde = Web.Contents("https://api.bilagscan.dk/oauth2/token",
[Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(Uri.BuildQueryString([grant_type="password", client_id="XXXXXXXXXXXXXXXXXXXXXXXXXX", client_secret="XXXXXXXXXXXXXXXXXXXXXXXXXX", email="XXXXX@YYYYYYYYYY.com", password="XXXXXXXXXXXXX"]))]),
    KildeTekst = Text.FromBinary(Kilde),
    KildeSegmenter = Splitter.SplitTextByDelimiter("&")(KildeTekst),
    KildeKvps = List.Transform(    KildeSegmenter, 
                                       (value) => { 
                                          List.First((Splitter.SplitTextByDelimiter("=")(value))),
                                          List.Last((Splitter.SplitTextByDelimiter("=")(value)))
                                        }
                                  ),
    KildeKvpTabel = Table.FromList(KildeSegmenter, Splitter.SplitTextByDelimiter("=")),
    #"Filtrerede rækker" = Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"}),
    Token = Text.From(List.First(Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"})[Column2])),

    EntitiesPerPage = 100,
    BaseUrl = "https://api.XXXXXXXXXXX.dk/v1/organizations/XXXX/vouchers",
    Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],


        GetJsonCount = (Url) =>
        let 
            result = Web.Contents(Url, Options),
            #"Temp" = Json.Document(result,1252),
            JsonCount = Record.Field(#"Temp"[meta],"count")
        in  JsonCount,

 

        
        GetJsonList = (Url) =>
        let 
            resultList = Web.Contents(Url, Options),
            #"TempList" = Json.Document(resultList,1252),
            JsonList = #"TempList"[data]
        in  JsonList,
        
        GetEntityCount = () =>
        let
            Url   = BaseUrl & "?count=11",
            Json = GetJsonCount(Url),
            Count = Json
        in  Count,

 

        
        GetPage = (Index) =>
        let     
            Skip  = "offset=" & Text.From(Index * EntitiesPerPage),
            Top   = "?count=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Top & "&" & Skip,
            Json  = GetJsonList(Url),
            Value = Json[#"value"]
        in  Value,

 

    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),

 

    PageIndices = { 0 .. PageCount - 1 },
    
   Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table    = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

I really hope someone out there can help us out 🙂

Best regards

[Shamatix]

Thanks a ton for trying to help.

Sadly the code you posted doesnt work =/

Expression.Error: 2 arguments were passed to a function which expects 1.

Still getting the same error.

Wish there were some better form of error handling in this product :S