Data source Settings issue is "some data sources may not be listed because of hand-authored queries

[Anonymous]

Hi Team,
1. For pagination Approach in Power BI we use the following Power Query to load the data from our API.
2. Pagination approach is based on Start index.
3. We get issue in Data source Settings as "some data sources may not be listed because of hand-authored queries", when we use this GetSource(Url) method (pagination call).

Kindly help us to resolve this issue.
Please let us know for more information and clarifications.

let
BaseUrl = "https://"& #"Base Url" &"/app/dao/v6/employees?a=getAll&numRecords=100&apiKey="& #"API Key" &"&accessKey=" & #"Access Key",
EntitiesPerPage = 100,
Source = Json.Document(Web.Contents(BaseUrl)),

GetTotalEntities = () =>
let Json = Source,
Total = Json[countOfRecords]
in Total,

GetPage = (Index) =>
let Skip = "startIndex=" & Text.From(Index * EntitiesPerPage),
BaseUrl = BaseUrl & "&" & Skip
in BaseUrl,

GetSource = (Url) =>
let Source = Json.Document(Web.Contents(Url))
in Source,

EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },

URLs = List.Transform(PageIndices, each GetPage(_)),
Pages = List.Transform(URLs, each GetSource(_)),
#"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data"}, {"Column1.data"}),
#"Expanded Column1.data" = Table.ExpandListColumn(#"Expanded Column1", "Column1.data"),
#"Expanded Column1.data1" = Table.ExpandRecordColumn(#"Expanded Column1.data", "Column1.data", {"employeeNumber", "fullName"}, {"Column1.data.employeeNumber", "Column1.data.fullName"})
in
#"Expanded Column1.data1"

Thanks in Advance.

Issue in Data Source Settings

[Anonymous]

Hi Eyelyn Qin,

    Thanks for your Support. As per your suggestion we tried and fix the data source issue. Now using like this [Query=[startIndex=IndexValues]] in Url.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Such error refers to the output step in the query that calls the Package Show endpoint with the dynamically-generated url.

Closing this dialog and going back to the Query Editor, if you click the Edit Credentials button, you can set credentials for the data source (anonymous access is fine in this case). For more information, please refer to:Credentials, Data Privacy Settings And Data Sources In Power Query/Power BI

These articles might hold some useful tips: 

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.