[unable to combine data] Please rebuild this data combination - Invoking custom function

[aTChris]

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 

[hannahschwed]

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:

[aTChris]

@hannahschwed 

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.

[aTChris · Accepted 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"