cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aTChris
Resolver I
Resolver I

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

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 

1 ACCEPTED SOLUTION
aTChris
Resolver I
Resolver I

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"

 

View solution in original post

1 REPLY 1
aTChris
Resolver I
Resolver I

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"

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors