Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.