JSON Joining Records in Groups

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:

Hi ImkeF v-huizhn-msft

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....Expanded data 8 times to reveal entries

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