CODEWITHSUNDEEP
powerbipowerquery
Vikas Lalwani
Vikas Lalwani

Reputation: 1061

Pagination is not working in Power BI M Query

I am trying to call an API using m query, to create custom connector in Power BI.

Here is my M Query

shared ConnectWiseManage.Contents = () => ConnectWiseManageNavTable(BaseUrl) as table;

ConnectWiseManageNavTable = (url as text) as table =>

let
    entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
    rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
    withData = Table.AddColumn(rename, "Data", each ConnectWiseManage.Feed(Uri.Combine(url, [Name])), Uri.Type),
    withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
    withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
    withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
    navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
    navTable;

//Basic Auth Implementation

ConnectWiseManage.Feed = (url as text) =>
  let
    Page = if url = null then {} meta [nextUrl = null] else
        let
            PasswordAPIKey = Text.Split(Extension.CurrentCredential()[Password], "+"),
            username = Extension.CurrentCredential()[Username],
            userpass = List.First(PasswordAPIKey),
            basicAuth = Binary.ToText(Text.ToBinary(Text.Combine({username, ":", userpass}))),
            source = Web.Contents(url,
                [
                    ManualCredentials = true,
                    Headers = [#"Authorization" = Text.Combine({"Basic ", basicAuth}), #"clientid" = clientid]
                    //ManualStatusHandling = {403, 404} // Useful for debugging.
                ]),
            content =source,
            nextUrl = GetNextLink(content),
            objects = Json.Document(content),
           // json = Json.Document(source)
             asTable = Table.FromList(objects, Splitter.SplitByNothing()),
             fields = Record.FieldNames(Table.FirstValue(asTable, [Empty = null])),
             expandAll = Table.ExpandRecordColumn(asTable, "Column1", fields)
            
   in
            expandAll meta [nextUrl = nextUrl]

            in
    Page;


 GetNextLink = (response) =>
  let
    // The "Link" header is not accessible in Power Query, hence this Data Connector.
    link = Value.Metadata(response)[Headers][Link]?,
    links = Text.Split(link, ","),
    splitLinks = List.Transform(links, each Text.Split(Text.Trim(_), ";")),
    next = List.Select(splitLinks, each Text.Trim(_{1}) = "rel=""next"""),
    first = List.First(next),
    removedBrackets = Text.Range(first{0}, 1, Text.Length(first{0}) - 2)
in
    try removedBrackets otherwise null;

 //Common functions 


 Table.ToNavigationTable = (
    table as table,
   keyColumns as list,
   nameColumn as text,
   dataColumn as text,
   itemKindColumn as text,
   itemNameColumn as text,
   isLeafColumn as text
 ) as table =>
  let
    tableType = Value.Type(table),
    newTableType = Type.AddTableKey(tableType, keyColumns, true) meta 
    [
        NavigationTable.NameColumn = nameColumn, 
        NavigationTable.DataColumn = dataColumn,
        NavigationTable.ItemKindColumn = itemKindColumn, 
        Preview.DelayColumn = itemNameColumn, 
        NavigationTable.IsLeafColumn = isLeafColumn
    ],
    navigationTable = Value.ReplaceType(table, newTableType)
in
    navigationTable;

When executing above m query and using it as a connector in Power BI, i am only getting first page's 25 results, not getting second page data or any other page data.

There are total 474 pages.

Header -> "Link", showing next page and last page, when calling api using Postman

<https://demo.example.com/v4_6_release/apis/3.0/company/contacts?pageSize=25&page=2>; rel="next", <https://demo.example.com/v4_6_release/apis/3.0/company/contacts?pageSize=25&page=474>; rel="last"

I am not sure how to make it work and what's wrong, as I don't have much experience in M Query, any help is appreciated.

Upvotes: 1

Views: 1040

Answers (1)

Vikas Lalwani
Vikas Lalwani

Reputation: 1061

Finally, I was able to implement paging in a different manner using List.Generate()

let
 Expand= List.Generate(()=>
   [Result= try GetData(1) otherwise null, Page = 1],
each [Result] <> null,
each [Result = try GetData([Page]+1) otherwise null, Page =[Page]+1],
each [Result]),
tableOfPages = Table.FromList(Expand, Splitter.SplitByNothing(), {"Column1"}),

ExpandAll=  Table.ExpandTableColumn(tableOfPages, "Column1", {"id","firstName","lastName","company","site","title"})
   

 in
   ExpandAll;

Where GetData is

 GetData = (page as number)  =>
  if page < 10 then
    let
  
        MainString= Text.Combine({"https://example.domain.com/v4_6_release/apis/3.0/company/contacts?page=",Number.ToText(page)}),
        CompleteString=Text.Combine({MainString,"&fields=id,firstName,lastName,company,site,title"}),
    Source = Json.Document(Web.Contents( CompleteString,  
                            [ManualCredentials = true,
                         Headers = [#"Authorization" = Text.Combine({"Basic ", "Token"})]]))
     in
        Source
else null;

I have limited code to fetch for 10 pages only for testing purpose, but removing if-else condition from GetData will make it fetch all pages.

Upvotes: 2

Related Questions