Forum Discussion

rachaelwalker's avatar
rachaelwalker
Resolver III
5 years ago
Solved

Expression.Error: We cannot convert the value null to type List.

I created a query calling a custom function, and it is giving me the following error. I am connecting via API using a static URL   Expression.Error: We cannot convert the value null to type List. ...
Need Help
  • rachaelwalker's avatar
    rachaelwalker
    5 years ago

    mahoneypat Anonymous I was able to resolve all issues replacing the "&" with "?". I am not sure of the reasoning behind it but I appreciate your time and help!

     

     

     

     

     

rachaelwalker's avatar
rachaelwalker
Resolver III
5 years ago

Additional information that may be helpful. I am also able to pull data using that same data source with a single query but it has the 1k record limitation. The list has columns and nested records and I am able to use power bi to expand them all. 

 

 

 

let
    Source = Json.Document(Web.Contents("https://" & "api-na." & "myconnectwise.net/" & "v4_6_release/apis/3.0/" & "service/tickets?pageSize=1000", [Headers=[Authorization="Basic <token>", clientID="<id>"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "summary", "recordType", "board", "status", "company", "site", "siteName", "addressLine1", "city", "stateIdentifier", "zip", "contact", "contactName", "contactPhoneNumber", "contactEmailAddress", "type", "team", "priority", "serviceLocation", "agreement", "severity", "impact", "allowAllClientsPortalView", "customerUpdatedFlag", "automaticEmailContactFlag", "automaticEmailResourceFlag", "automaticEmailCcFlag", "closedDate", "closedBy", "closedFlag", "approved", "estimatedExpenseCost", "estimatedExpenseRevenue", "estimatedProductCost", "estimatedProductRevenue", "estimatedTimeCost", "estimatedTimeRevenue", "billingMethod", "subBillingMethod", "dateResolved", "dateResplan", "dateResponded", "resolveMinutes", "resPlanMinutes", "respondMinutes", "isInSla", "resources", "hasChildTicket", "hasMergedChildTicketFlag", "billTime", "billExpenses", "billProducts", "location", "department", "mobileGuid", "sla", "slaStatus", "currency", "_info", "customFields", "country", "actualHours", "source", "poNumber", "workRole", "requiredDate", "addressLine2", "billingAmount", "automaticEmailCc", "workType", "hourlyRate", "contactPhoneExtension", "opportunity", "subType", "budgetHours", "owner", "estimatedStartDate", "duration", "parentTicketId", "mergedParentTicket", "subBillingAmount"}, {"Column1.id", "Column1.summary", "Column1.recordType", "Column1.board", "Column1.status", "Column1.company", "Column1.site", "Column1.siteName", "Column1.addressLine1", "Column1.city", "Column1.stateIdentifier", "Column1.zip", "Column1.contact", "Column1.contactName", "Column1.contactPhoneNumber", "Column1.contactEmailAddress", "Column1.type", "Column1.team", "Column1.priority", "Column1.serviceLocation", "Column1.agreement", "Column1.severity", "Column1.impact", "Column1.allowAllClientsPortalView", "Column1.customerUpdatedFlag", "Column1.automaticEmailContactFlag", "Column1.automaticEmailResourceFlag", "Column1.automaticEmailCcFlag", "Column1.closedDate", "Column1.closedBy", "Column1.closedFlag", "Column1.approved", "Column1.estimatedExpenseCost", "Column1.estimatedExpenseRevenue", "Column1.estimatedProductCost", "Column1.estimatedProductRevenue", "Column1.estimatedTimeCost", "Column1.estimatedTimeRevenue", "Column1.billingMethod", "Column1.subBillingMethod", "Column1.dateResolved", "Column1.dateResplan", "Column1.dateResponded", "Column1.resolveMinutes", "Column1.resPlanMinutes", "Column1.respondMinutes", "Column1.isInSla", "Column1.resources", "Column1.hasChildTicket", "Column1.hasMergedChildTicketFlag", "Column1.billTime", "Column1.billExpenses", "Column1.billProducts", "Column1.location", "Column1.department", "Column1.mobileGuid", "Column1.sla", "Column1.slaStatus", "Column1.currency", "Column1._info", "Column1.customFields", "Column1.country", "Column1.actualHours", "Column1.source", "Column1.poNumber", "Column1.workRole", "Column1.requiredDate", "Column1.addressLine2", "Column1.billingAmount", "Column1.automaticEmailCc", "Column1.workType", "Column1.hourlyRate", "Column1.contactPhoneExtension", "Column1.opportunity", "Column1.subType", "Column1.budgetHours", "Column1.owner", "Column1.estimatedStartDate", "Column1.duration", "Column1.parentTicketId", "Column1.mergedParentTicket", "Column1.subBillingAmount"}),
    #"Expanded Column1.board" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.board", {"id", "name"}, {"Column1.board.id", "Column1.board.name"})
in
    #"Expanded Column1.board"