none
Pagination & Looping Record Requests RRS feed

  • Question

  • Hi Community,

    Thanks in advance for any help with this problem i am having.

    As a background i have the following information:

    I am writing some reports using Power BI and Accounting software API that returns a Json file. 

    The API has a default page return of 400 records per request and a max of 1,000. I need to combine 9766 journal records.

    I have read many posts about Pagination and Looping however i am struggling with the last part of the equation that gathers the page urls and combines the item record lists for each set of calls for the data.

    My current code i have is returning error:

    Expression.Error: we cannot convert the value "http://localhost:808..." to type list.

    Details: 
        Value=http://localhost:8080/AccountRight/b912ff4a-29f0-4d2a-a93b-2a6a3200a2bf/GeneralLedger/JournalTransaction/?api-version=v2&$top=1000&$skip=0
        Type=Type

    Here is the full query

    let
        BaseUrl = "http://localhost:8080/AccountRight/b912ff4a-29f0-4d2a-a93b-2a6a3200a2bf/GeneralLedger/JournalTransaction/?api-version=v2&$top=1000",
        EntitiesPerPage = 1000,

        GetJson = (Url) =>
            let RawData = Web.Contents(Url),
                Json = Json.Document(RawData)
            in  Json,

       GetTotalEntities = () =>
        let Json = Json.Document(Web.Contents("http://localhost:8080/AccountRight/b912ff4a-29f0-4d2a-a93b-2a6a3200a2bf/GeneralLedger/JournalTransaction/?api-version=v2")),
        Items = Json[Count]
        in
        Items,

        GetPage = (Index) =>
            let skip = "$skip=" & Text.From(Index * EntitiesPerPage),
                Url = BaseUrl & "&" & skip
            in Url,

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

        URLs = List.Transform(PageIndices, each GetPage(_)),
        Pages    = List.Transform(PageIndices, each GetPage(_)),
        Entities = List.Union(Pages)
    in
        Entities

    Anyway, thanks for any assistance....and Merry Xmas!


    Wednesday, December 27, 2017 4:16 AM

Answers

  • Thanks Garry,

    the link to your other post helped.

    The key to the solution is to call your function in a Table.AddColumn-command. Please copy this code into the advanced editor and follow each step. You will see how the list will be transformed to a table (#"Converted to Table")  and then a new column is added (#"Added Custom") where the function is then called for every row. Final expansion follows the strange format of the actual content, these steps are best done using the UI for your own data.

    let
        MyFunction = (_) => List.Repeat({[Variable = "This is batch "& Text.From(_), Fix = "A"] },3),
        MyList = {1..10},
        #"Converted to Table" = Table.FromList(MyList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "ExecuteFunction", each MyFunction([Column1])),
        #"Expanded ExecuteFunction" = Table.ExpandListColumn(#"Added Custom", "ExecuteFunction"),
        #"Expanded ExecuteFunction1" = Table.ExpandRecordColumn(#"Expanded ExecuteFunction", "ExecuteFunction", {"Variable", "Fix"}, {"Variable", "Fix"})
    in
    #"Expanded ExecuteFunction1"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by TheG72 Wednesday, December 27, 2017 1:24 PM
    • Unmarked as answer by TheG72 Wednesday, December 27, 2017 1:25 PM
    • Marked as answer by TheG72 Wednesday, December 27, 2017 1:27 PM
    Wednesday, December 27, 2017 9:51 AM
    Moderator

All replies

  • Hi TheG72,

    List.Union expects a list of lists as an input, but it seems to receive a list of URL-strings (text-elements) instead, as your function GetPage returns a text-string. Could it be that you used the wrong formula in step "Pages"?

    How about this instead:

        Pages    = List.Transform(URLs, each GetJson(_)),

    Then delete your last step Entities and expand manually using the UI: Transform your list to a table and click on the arrows that will pop up.  


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, December 27, 2017 8:01 AM
    Moderator
  • Hi Imke,

    Thanks for taking the time to answer my post, I really appreciate your time you have devoted to the subject matter!...

    I was unsure how to reference the URL list...and was following previous posts about this matter but my API is actually the MYOB AccountRight one shown here http://developer.myob.com/api/accountright/api-overview/retrieving-data/#TOP

    Once, i modified the line i see 10 records for each url. 

    Each Record (1-10) shows:
    Items List (Expandale data records)
    NextPageLink (Url for next set of 1,000 records)
    Count 9766 (shows total records)

    I need the work out how to grab each records Items List(holding 1,000 journal transactions each) and Expand and combine?

    How should i proceed from this step...If i click row 1 RECORD then i get the data for that first 1,000 records...do i need to go back again to this point to retrieve the next 1,000?

    I am just waiting to validate my account so i can post pictures but the post is on Power BI community under heading "Json Joining Records in Groups" with screen shots which may help.

    https://community.powerbi.com/t5/Desktop/JSON-Joining-Records-in-Groups/m-p/329269/highlight/false#M147086

    Thanks Again and Merry Xmas to you

    Cheers

    Garry



    • Edited by TheG72 Wednesday, December 27, 2017 9:58 AM edit
    Wednesday, December 27, 2017 9:27 AM
  • Thanks Garry,

    the link to your other post helped.

    The key to the solution is to call your function in a Table.AddColumn-command. Please copy this code into the advanced editor and follow each step. You will see how the list will be transformed to a table (#"Converted to Table")  and then a new column is added (#"Added Custom") where the function is then called for every row. Final expansion follows the strange format of the actual content, these steps are best done using the UI for your own data.

    let
        MyFunction = (_) => List.Repeat({[Variable = "This is batch "& Text.From(_), Fix = "A"] },3),
        MyList = {1..10},
        #"Converted to Table" = Table.FromList(MyList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "ExecuteFunction", each MyFunction([Column1])),
        #"Expanded ExecuteFunction" = Table.ExpandListColumn(#"Added Custom", "ExecuteFunction"),
        #"Expanded ExecuteFunction1" = Table.ExpandRecordColumn(#"Expanded ExecuteFunction", "ExecuteFunction", {"Variable", "Fix"}, {"Variable", "Fix"})
    in
    #"Expanded ExecuteFunction1"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by TheG72 Wednesday, December 27, 2017 1:24 PM
    • Unmarked as answer by TheG72 Wednesday, December 27, 2017 1:25 PM
    • Marked as answer by TheG72 Wednesday, December 27, 2017 1:27 PM
    Wednesday, December 27, 2017 9:51 AM
    Moderator
  • Hi Thanks for your response, i did update that link in power BI with some new screens too after this update below...

    Okay i can paste your code in and follow each step but i am a fairly new to Power Query so i don't understand how to "call your function in a Table.AddColumn-command".... using the data i have queried for the 10 items in the list...

    The first step applied is the MyFunction it prompts for an optional Parameter, I can see how the steps you have setup can guide me through how to apply this function to the data...I will have another go with this information, I have added image to  power bi with this latest update....and will refer others to this and the community post on Power BI.

    Thankyou again for all your great work and best wishes for the New Year Imke.



    • Edited by TheG72 Wednesday, December 27, 2017 1:26 PM Edit comments
    Wednesday, December 27, 2017 10:19 AM