Hello,
I have been looking for a solution to loop thgough all values in a table but I keep running into an expression error: We cannot convert a value of type list to type Function.
I'm trying to do something very simmiler to this article but when following the first link I'm stuck at creating a new column. I also tryed the second link they provided but I still get stuck at the same spot with the same error.
Here are my 3 queries I have.
let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
each List.Count([WebCall])>0 or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/company/companies?pagesize=1000&page="&Text.From([Page])&"", [Headers=[Authorization="KEY", ContentType="application/json"]])), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"Column1.WebCall", "Column1.Page", "Column1.Counter"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.Page", "Column1.Counter"}),
#"Expanded Column1.WebCall" = Table.ExpandListColumn(#"Removed Columns", "Column1.WebCall"),
#"Expanded Column1.WebCall1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall", "Column1.WebCall", {"id", "identifier", "name", "status", "type", "addressLine1", "addressLine2", "city", "state", "zip", "country", "phoneNumber", "faxNumber", "website", "territoryId", "accountNumber", "dateAcquired", "sicCode", "annualRevenue", "timeZone", "leadFlag", "unsubscribeFlag", "userDefinedField5", "taxCode", "billingTerms", "invoiceDeliveryMethod", "deletedFlag", "dateDeleted", "deletedBy", "mobileGuid", "currency", "_info", "customFields", "marketId", "defaultContact", "vendorIdentifier", "taxIdentifier", "billToCompany", "billingSite", "billingContact", "territoryManager", "invoiceToEmailAddress", "numberOfEmployees", "invoiceCCEmailAddress", "linkedInUrl", "facebookUrl", "twitterUrl"}, {"Column1.WebCall.id", "Column1.WebCall.identifier", "Column1.WebCall.name", "Column1.WebCall.status", "Column1.WebCall.type", "Column1.WebCall.addressLine1", "Column1.WebCall.addressLine2", "Column1.WebCall.city", "Column1.WebCall.state", "Column1.WebCall.zip", "Column1.WebCall.country", "Column1.WebCall.phoneNumber", "Column1.WebCall.faxNumber", "Column1.WebCall.website", "Column1.WebCall.territoryId", "Column1.WebCall.accountNumber", "Column1.WebCall.dateAcquired", "Column1.WebCall.sicCode", "Column1.WebCall.annualRevenue", "Column1.WebCall.timeZone", "Column1.WebCall.leadFlag", "Column1.WebCall.unsubscribeFlag", "Column1.WebCall.userDefinedField5", "Column1.WebCall.taxCode", "Column1.WebCall.billingTerms", "Column1.WebCall.invoiceDeliveryMethod", "Column1.WebCall.deletedFlag", "Column1.WebCall.dateDeleted", "Column1.WebCall.deletedBy", "Column1.WebCall.mobileGuid", "Column1.WebCall.currency", "Column1.WebCall._info", "Column1.WebCall.customFields", "Column1.WebCall.marketId", "Column1.WebCall.defaultContact", "Column1.WebCall.vendorIdentifier", "Column1.WebCall.taxIdentifier", "Column1.WebCall.billToCompany", "Column1.WebCall.billingSite", "Column1.WebCall.billingContact", "Column1.WebCall.territoryManager", "Column1.WebCall.invoiceToEmailAddress", "Column1.WebCall.numberOfEmployees", "Column1.WebCall.invoiceCCEmailAddress", "Column1.WebCall.linkedInUrl", "Column1.WebCall.facebookUrl", "Column1.WebCall.twitterUrl"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1.WebCall1",{"Column1.WebCall.id"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each [Column1.WebCall.id]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
Custom = #"Changed Type"[Custom]
in
Custom
let CWManageID = (Column1.WebCall1.id) =>
let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
each List.Count([WebCall])>0 or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/company/companies?pagesize=1000&page="&Text.From([Page])&"", [Headers=[Authorization="KEY", ContentType="application/json"]])), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"Column1.WebCall", "Column1.Page", "Column1.Counter"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.Page", "Column1.Counter"}),
#"Expanded Column1.WebCall" = Table.ExpandListColumn(#"Removed Columns", "Column1.WebCall"),
#"Expanded Column1.WebCall1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall", "Column1.WebCall", {"id", "identifier", "name", "status", "type", "addressLine1", "addressLine2", "city", "state", "zip", "country", "phoneNumber", "faxNumber", "website", "territoryId", "accountNumber", "dateAcquired", "sicCode", "annualRevenue", "timeZone", "leadFlag", "unsubscribeFlag", "userDefinedField5", "taxCode", "billingTerms", "invoiceDeliveryMethod", "deletedFlag", "dateDeleted", "deletedBy", "mobileGuid", "currency", "_info", "customFields", "marketId", "defaultContact", "vendorIdentifier", "taxIdentifier", "billToCompany", "billingSite", "billingContact", "territoryManager", "invoiceToEmailAddress", "numberOfEmployees", "invoiceCCEmailAddress", "linkedInUrl", "facebookUrl", "twitterUrl"}, {"Column1.WebCall.id", "Column1.WebCall.identifier", "Column1.WebCall.name", "Column1.WebCall.status", "Column1.WebCall.type", "Column1.WebCall.addressLine1", "Column1.WebCall.addressLine2", "Column1.WebCall.city", "Column1.WebCall.state", "Column1.WebCall.zip", "Column1.WebCall.country", "Column1.WebCall.phoneNumber", "Column1.WebCall.faxNumber", "Column1.WebCall.website", "Column1.WebCall.territoryId", "Column1.WebCall.accountNumber", "Column1.WebCall.dateAcquired", "Column1.WebCall.sicCode", "Column1.WebCall.annualRevenue", "Column1.WebCall.timeZone", "Column1.WebCall.leadFlag", "Column1.WebCall.unsubscribeFlag", "Column1.WebCall.userDefinedField5", "Column1.WebCall.taxCode", "Column1.WebCall.billingTerms", "Column1.WebCall.invoiceDeliveryMethod", "Column1.WebCall.deletedFlag", "Column1.WebCall.dateDeleted", "Column1.WebCall.deletedBy", "Column1.WebCall.mobileGuid", "Column1.WebCall.currency", "Column1.WebCall._info", "Column1.WebCall.customFields", "Column1.WebCall.marketId", "Column1.WebCall.defaultContact", "Column1.WebCall.vendorIdentifier", "Column1.WebCall.taxIdentifier", "Column1.WebCall.billToCompany", "Column1.WebCall.billingSite", "Column1.WebCall.billingContact", "Column1.WebCall.territoryManager", "Column1.WebCall.invoiceToEmailAddress", "Column1.WebCall.numberOfEmployees", "Column1.WebCall.invoiceCCEmailAddress", "Column1.WebCall.linkedInUrl", "Column1.WebCall.facebookUrl", "Column1.WebCall.twitterUrl"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1.WebCall1",{"Column1.WebCall.id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1.WebCall.id", type text}})
in
#"Changed Type"
in CWManageID
let
Source = {1..3},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "CWManageCompanyID", each [Column1]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CWManageCompanyID"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "CWManageComanyID", each fManageComanyID (2)([Column1.WebCall1.id])),
CWManageComanyID = #"Added Custom1"{2}[CWManageComanyID]
in
CWManageComanyID
I'm not to fimiler with coding. This is also my first attempt at Power BI and pulling data frmo an API. I've managed to get past the 1000 items Pagination. I really need to do exactley what this post is doing. I'm just not sure how to get there or if I'm on the right track. According to Connectwise documentation I would need to add this to the url /company/companies/{id}/sites. I get the ID's for each company in my first query.
Any help is appriciated.
