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

[rachaelwalker]

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.
Details:
Value=
Type=[Type]

Any ideas how I can resolve this error?

Custom Function PageData

let
    Source = (PageNumber as number) => let
    Source = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets",[RelativePath="&pagesize=1000&Page="&Number.ToText(PageNumber),Headers=[Authorization="Basic <auth token here>",clientID="<client ID here>", ContentType="JSon"]]))
in
    Source
in
    Source

Query

let
    Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result= try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1], each [Result])
in
    Source

If I dont use a static URL I am able to transform the list to a table just fine but doesnt support auto refresh

PageData that works

let
    Source = (PageNumber as number) => let Source = Json.Document(Web.Contents("https://" & "api-na." & "myconnectwise.net/" & "v4_6_release/apis/3.0/" & "service/tickets?pageSize=1000&page="&Number.ToText(PageNumber), [Headers=[Authorization="Basic <auth token here>", clientID="<client ID here>"]]))
    
in Source
    

in Source

[mohdmohideen200]

By carefully examining your query, identifying the source of the null values, and applying the appropriate solutions (like replacing null with an empty list or using conditional logic), you can resolve this "cannot convert the value null to type List" error in Power Query. The key is to be proactive in handling potential null values before they cause errors.

[mahoneypat]

Please see this article.  You want to put your query parameters (the stuff after the ?) in the Query clause, not in the RelativePath.

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

let
    Source = (PageNumber as number) => let
    Source = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets",[Query=[pagesize="1000", Page=Number.ToText(PageNumber)],Headers=[Authorization="Basic <auth token here>",clientID="<client ID here>", ContentType="JSon"]]))
in
    Source
in
    Source

Regards,

Pat

[rachaelwalker]

Thank you! I was able to convert it to a list however I am back at square one where it is only pulling 1k records. Do you know why it is not retrieving the next page of records?

[mahoneypat]

Since you have the count condition in your List.Generate already, I'm not sure why you need the try ... otherwise.  I would try it w/o that.  Also, if you know how many records to expect, you could hardcode a list of numbers in, instead of using List.Generate (at least for troubleshooting).

Pat

[rachaelwalker]

@mahoneypat can you tell me which try otherwise I should remove. I tried removing the one highlighted in yellow but now getting same error cannot convert to list. I appreciate your time and help. I know this is difficult not fully knowing my data source.  

let
    Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result= try PageData(PageNumber) otherwise null, PageNumber = PageNumber +1], each [Result]),

[rachaelwalker · Accepted solution]

@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!

[Anonymous]

PageNumber is defined as a number, so you should not have [] surrounding PageNumber.  So this: [PageNumber] should be replaced by just PageNumber.

Also, where is the definition of PageData?

Hope this helps!--Nate

Hope that helps!--Nate

[rachaelwalker]

I removed the [] for both PageNumber in Query 1 and same error. I feel like the error is in the fx because it worked until I changed the fx to use a static URL. Is this what you mean by definition? screenshot below

I removed the [] for both PageNumber and same error. I feel like the error is in the fx because it worked until I changed the fx to use a static URL. Is this what you mean by PageData definition?

[rachaelwalker]

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"