March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
Very new to Power BI but having some issues getting data via JSON, the first page is imported but subsequent pages are ignored. I have followed a couple of tutorials but can't seem to find the issue here:
https://cloudposapi.citruslime.com/swagger/ui/index
let
Source = Json.Document(Web.Contents("https://cloudposapi.citruslime.com/api/CustomerOrder", [Headers=[Authorization="xx"]])),
BaseUrl = "https://cloudposapi.citruslime.com/api/CustomerOrder",
EntitiesPerPage = 1000,
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetTotalEntities = () =>
let Json = GetJson(BaseUrl),
Total = Json[total]
in Total,
GetPage = (Index) =>
let Skip = "skip=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip,
Json = GetJson(Url),
Value = Json[data]
in Value,
GetUrl = (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 GetUrl(_)),
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Uid", "ClientRef", "Closed", "DateCreated", "TypeAsString", "Type", "Comment", "CustomerID", "ShipToID", "Deposit", "Tax", "Total", "LastUpdated", "DueDate", "Taxable", "SalesRepID", "ReferenceNumber", "ShippingChargeOnOrder", "ChannelTypeAsString", "ChannelType", "CheckedIn", "CustomerName", "OrderLines"}, {"Uid", "ClientRef", "Closed", "DateCreated", "TypeAsString", "Type", "Comment", "CustomerID", "ShipToID", "Deposit", "Tax", "Total", "LastUpdated", "DueDate", "Taxable", "SalesRepID", "ReferenceNumber", "ShippingChargeOnOrder", "ChannelTypeAsString", "ChannelType", "CheckedIn", "CustomerName", "OrderLines"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Total", Currency.Type}})
in
#"Changed Type"
Hi @Anonymous ,
I can't test this in my environment because of the authentication. You could check each step to see which step can't work. By the way, you also could @ super users for more suggestions.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
55 | |
26 | |
17 | |
13 |