Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community,
I am trying to retrieve records from an API that returns a Json formatted output in max groups of 1,000 records per request.
I have 10,000 journal records to get so i query the API to get each group of 1,000 records via incrementing the SKIP step.
Query1
let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/FileID/GeneralLedger/JournalTransaction/?$top=1000"))
in
Source
Query2
let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/FileID/GeneralLedger/JournalTransaction/?$top=1000&$skip=1000"))
in
Source
So i ended up with 9 queries for each group of 1000 records and then a Table.Combine to consolidated tables into one master listing....it was probably an inefficent way to do this.
I was reading Chris Webb and ImkeF posts about solving types of issues with API pagination limits and using functions to automate the get data part but i need some help with getting the list of journals to combine.
I wasnt sure best way to complete next step.
I am given then number count of records and nextpageLink for the URI for next 1000 journals.
I can generate a query to return the number of records
let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/FileId/GeneralLedger/JournalTransaction/")),
Items = Source[Count]
in
Items
This returns a value of 9766 shown in the source datawindow above. So if i divide this by 1000 i get the number of page calls i need to make to get all journal transactions.
I can now create a function to grab records by entering a SkipValue, so that seems to work okay...
(SkipValue as text) =>
let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/FileId/GeneralLedger/JournalTransaction/?$stop=1000&$skip=" & SkipValue "))
in
Source
How do i integrate the function with data so i can combine this into 1 table dynamically ?
How do i get the Skip values required for each source page of data lists?
Can anyone point me in right direction?
Thanks again for any assistance...and Merry Xmas everyone
Solved! Go to Solution.
Hi @THEG72,
After research, there is a blog discribed how to implement REST API pagination in Power Query, and similar thread for your reference.
How To Do Pagination In Power Query
Creating a loop for fetching paginated data from a REST API
Best Regards,
Angelia
Hi Garry, thanks for the pics. If your list contains all the data already, there is no need to use the other technique I've suggested. Just do the following:
1) Delete the steps after "Pages"
2) Transform your list to a table:
3) Expand the column by clicking on the arrows (1) and deselect the other fields (2) like this:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks for this....it combined as you have advised and i have expanded the records containing the data to reveal all the 9.766 records....You are a STAR :)...I got lost a bit on this as you are right this structured Json file is odd....
I had to expand the data many times to extract the final information
Here is the final code
let
BaseUrl = "http://localhost:8080/AccountRight/FileID/GeneralLedger/",
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/fileID/GeneralLedger/")),
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(URLs, each GetJson(_)),
#"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Items"}, {"Column1.Items"}),
#"Expanded {0}1" = Table.ExpandListColumn(#"Expanded {0}", "Column1.Items"),
#"Expanded {0}2" = Table.ExpandRecordColumn(#"Expanded {0}1", "Column1.Items", {"UID", "DisplayID", "JournalType", "SourceTransaction", "DateOccurred", "DatePosted", "Description", "Lines", "URI", "RowVersion"}, {"UID", "DisplayID", "JournalType", "SourceTransaction", "DateOccurred", "DatePosted", "Description", "Lines", "URI", "RowVersion"}),
#"Expanded {0}3" = Table.ExpandRecordColumn(#"Expanded {0}2", "SourceTransaction", {"UID", "TransactionType", "URI"}, {"UID.1", "TransactionType", "URI.1"}),
#"Expanded {0}4" = Table.ExpandListColumn(#"Expanded {0}3", "Lines"),
#"Expanded {0}5" = Table.ExpandRecordColumn(#"Expanded {0}4", "Lines", {"Account", "Amount", "IsCredit", "Job", "LineDescription", "ReconciledDate"}, {"Account", "Amount", "IsCredit", "Job", "LineDescription", "ReconciledDate"}),
#"Expanded {0}6" = Table.ExpandRecordColumn(#"Expanded {0}5", "Job", {"UID", "Number", "Name", "URI"}, {"UID.2", "Number", "Name", "URI.2"}),
#"Expanded {0}7" = Table.ExpandRecordColumn(#"Expanded {0}6", "Account", {"UID", "Name", "DisplayID", "URI"}, {"UID.3", "Name.1", "DisplayID.1", "URI.3"})
in
#"Expanded {0}7"
I did a count of the UID's and it returned 9,766 dated from 2014 till November 2017 and the file size is about 2.7meg.
@ImkeF just another question if I can. would you recommend this method to capture the accounting transactions for historical periods or summarise this data? I know the loading of historical data can increase load time....If it can be summarised is this a better approach as more transactions are added?
Again, thanks for your expertise on the matter.....and Happy New Year everyone!
Hi Garry,
I had a different understanding of your actual data.
I believe you just need to test out different versions and see for yourself which one is faster at the end.
Imke Feldmann
www.TheBIccountant.com -- How to integrate M-code into your solution -- Check out more PBI- learning resources here
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |