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, can anyone help with this issue? @ImkeF this looks similar to the support you gave in another thread.
I have a JSON API with a max size of 1000. I have to cycle through the pages via API to collect all of the records. I have a script to get the pages, then a custom function to cycle through the pages. I call this from a query and it works great on Desktop but fails in the Cloud. Can anyone help me combine everything into one query?
ContractPages
let
Source = Json.Document(Web.Contents("https://xxx.xxxx.com/api/billing/coworkercontracts?size=1000")),
TotalPages1 = Source[TotalPages]
in
TotalPages1
GetContracts
(pPage as text) =>
let
gBaseUrl = "https://xxx.xxxx.com/api/billing/coworkercontracts",
gMaxSize = 1000,
vOptions = [Query=[size=Text.From(gMaxSize), page=pPage]],
Source = Json.Document( Web.Contents ( gBaseUrl, vOptions ) ),
Records = Source[Records],
#"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"}, {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"})
in
#"Expanded Column1"
Contracts Query
let
Source = List.Generate(() => 1, each _ <= (ContractPages), each _ + 1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Query", each GetContracts([Start])),
#"Expanded Query" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query", {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"}, {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"})
in
#"Expanded Query"
Thanks
Chris
Solved! Go to Solution.
I've managed to sort this myself. @ImkeF thanks for your post, that was the guide I needed.
let
ContractPages =
let
Source = Json.Document(Web.Contents("https://xxx.xxx.com/api/billing/coworkercontracts?size=1000")),
TotalPages1 = Source[TotalPages]
in
TotalPages1,
GetContracts = (pPage as text) =>
let
gBaseUrl = "https://xxx.xxx.com/api/billing/coworkercontracts",
gMaxSize = 1000,
vOptions = [Query=[size=Text.From(gMaxSize), page=pPage]],
Source = Json.Document( Web.Contents ( gBaseUrl, vOptions ) ),
Records = Source[Records],
#"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"}, {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"})
in
#"Expanded Column1",
Source = List.Generate(() => 1, each _ <= (ContractPages), each _ + 1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Query", each GetContracts([Start])),
#"Expanded Query" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query", {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"}, {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"})
in
#"Expanded Query"
I'm facing (apparently) the exact same issue, where my report doesn't refresh in Power BI Service and the issue is with a step that involves invoke custom function which calls an API (also the query itself calls the same API).
I'm trying to integrate the function inside the query text, but always get a syntax error. What am I doing wrong?
The error is saying "right parenthesis expected" and points to the first line.
combined query with function:
Hi, If I recall correctly this issue i had was because my solution was combining the data in different queries. Once I had everything other than the custom function within a single query and skipped the test on the schedule refresh within the service it worked.
I've managed to sort this myself. @ImkeF thanks for your post, that was the guide I needed.
let
ContractPages =
let
Source = Json.Document(Web.Contents("https://xxx.xxx.com/api/billing/coworkercontracts?size=1000")),
TotalPages1 = Source[TotalPages]
in
TotalPages1,
GetContracts = (pPage as text) =>
let
gBaseUrl = "https://xxx.xxx.com/api/billing/coworkercontracts",
gMaxSize = 1000,
vOptions = [Query=[size=Text.From(gMaxSize), page=pPage]],
Source = Json.Document( Web.Contents ( gBaseUrl, vOptions ) ),
Records = Source[Records],
#"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"}, {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"})
in
#"Expanded Column1",
Source = List.Generate(() => 1, each _ <= (ContractPages), each _ + 1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Query", each GetContracts([Start])),
#"Expanded Query" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query", {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"}, {"IssuedByName", "CoworkerId", "CoworkerFullName", "CoworkerCompanyName", "CoworkerBillingName", "TariffName", "StartDate", "BillingDay", "RenewalDate", "InvoicedPeriod", "ContractTerm", "Price", "Quantity", "CancellationDate", "CancellationLimitDays", "ProposalUniqueId", "FloorPlanDeskIds", "FloorPlanDeskNames", "TotalPrice", "UnitPrice", "Id"})
in
#"Expanded Query"
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.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |