Forum Discussion
Expression.Error: We cannot convert the value to type List
Hi Anonymous,
the problem is in addressing the row.
#"Specific Column" = Table.AddColumn(#"Changed Type4","Specific", each if [Column1]{[Index]-1} = [Column1]{[Index]-2} and [Column2]{[Index]-1} = [Column2]{[Index]-2} then [Amount]{[Index]-1} - [Amount]{[Index]-2} else [Amount]{[Index]-1})"Each" interates over all rows and gives you a current row in every step. And it means, that [Column1] is a text value in current row and you access (Index-1)th character of the string, what you actually don't want. Therefore the error, that a text isn't a list.
A suggestion is for example followings:
= Table.AddColumn(#"Changed Type 4", "Previous Row", each #"Changed Type 4"[Column1]{[Index] - 1})It addresses the column Column1 of (index-1)th row of the table #"Changed Type 4".
Btw. not sure how performant it is, but it works for sure
Hi, this is my first interaction with powerbi and creating an update schedule for data refresh. I am using a pbix file designed by the solution provider and this was working for a while, but now it is showing me an error message "We cannot convert the value null to type List". I have read through the post, but the article is not related to the issue I am experiencing. The error message only comes up when refreshing one eliment of the file the "Items" record. If someone has seen this issue with the ConnectWise SELL powerbi pbix file please could you reply to this if you were able to resolve. The expression is as follows:
let
PageSize = 10000,
Iterations = 1000,
BaseURL = "https://biapi.quosalsell.com/api/bi/items?take=1000&skip=0", //call this function recursively
GetItemPage = (page) => let
ItemSource = Json.Document(Web.Contents(BaseURL, [Query=[skip=Text.From(page * PageSize), take=Text.From(PageSize)], Timeout=#duration(0, 0, 10, 0)])),
Items = try ItemSource otherwise null
in
Items,
AllItems = List.Generate(
() => [i=0, response = GetItemPage(i)],
each [i]<=Iterations and List.IsEmpty([response])<>true,
each [i=[i]+1, response = Function.InvokeAfter(()=>GetItemPage(i), #duration(0,0,0,1))]
),
#"Converted to Table" = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"response"}, {"response"}),
#"Expanded response" = Table.ExpandListColumn(#"Expanded Column1", "response"),
#"Expanded response2" = Table.ExpandRecordColumn(#"Expanded response", "response", {"idQuoteItems", "idQuoteMain", "idQuoteTabs", "itemNumber", "manufacturerPartNumber", "quantity", "unitPrice", "unitCost", "extendedPrice", "extendedCost", "extendedMargin", "grossMarginPercent", "recurringAmount", "recurringCost", "recurringTotal", "description", "isOptional", "isTotalsIncluded", "isPrinted", "manufacturer", "vendor", "class", "category", "subCategory", "customDecimal1", "customDecimal2", "customString1", "customString2", "customBool1"}, {"idQuoteItems", "idQuoteMain", "idQuoteTabs", "itemNumber", "manufacturerPartNumber", "quantity", "unitPrice", "unitCost", "extendedPrice", "extendedCost", "extendedMargin", "grossMarginPercent", "recurringAmount", "recurringCost", "recurringTotal", "description", "isOptional", "isTotalsIncluded", "isPrinted", "manufacturer", "vendor", "class", "category", "subCategory", "customDecimal1", "customDecimal2", "customString1", "customString2", "customBool1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded response2",{{"quantity", type number}, {"unitPrice", Currency.Type}, {"unitCost", Currency.Type}, {"extendedPrice", Currency.Type}, {"extendedCost", Currency.Type}, {"extendedMargin", Currency.Type}, {"recurringAmount", Currency.Type}, {"recurringCost", Currency.Type}, {"recurringTotal", Currency.Type}, {"grossMarginPercent", type number}, {"isOptional", type logical}, {"isTotalsIncluded", type logical}, {"isPrinted", type logical}, {"customDecimal2", type number}, {"customDecimal1", type number}, {"customBool1", type logical}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"itemNumber", "Item Number"}, {"manufacturerPartNumber", "MFP#"}, {"quantity", "Quantity"}, {"unitPrice", "Unit Price"}, {"unitCost", "Unit Cost"}, {"extendedPrice", "Extended Price"}, {"extendedCost", "Extended Cost"}, {"extendedMargin", "Extended Margin"}, {"grossMarginPercent", "Gross Margin %"}, {"recurringAmount", "Recurring Amount"}, {"recurringCost", "Recurring Cost"}, {"recurringTotal", "Recurring Total"}, {"description", "Description"}, {"isOptional", "Optional"}, {"isTotalsIncluded", "Included in Totals"}, {"isPrinted", "Printed"}, {"manufacturer", "Manufacturer"}, {"vendor", "Vendor"}, {"class", "Class"}, {"category", "Category"}, {"subCategory", "Sub-Category"}, {"customDecimal1", "Custom Decimal 1"}, {"customDecimal2", "Custom Decimal 2"}, {"customString1", "Custom String 1"}, {"customString2", "Custom String 2"}, {"customBool1", "Custom Bool 1"}})
in
#"Renamed Columns"
I found with ConnectWise Sell, the solution was to reduce the PageSize and increase the Iterations:
i.e. replace
PageSize = 10000, Iterations = 1000,with
PageSize = 1000, Iterations = 10000,This will be slower to fetch data, but stable.
According to Connectwise's own API docs, requests are limited to 1000 results each, though it appears to be a soft limit in practise. The pbix file they supply requests too many results (10000) per page and so occasionally times out.
I used to get this error roughly a couple of times a month from automated refreshes on PowerBI.com, but not since making those changes about a year ago. I informed ConnectWise at the time and recommended they update the pbix file.
Anyway, appreciate my reply is probably too little too late, but maybe it will help someone else. Ironically, I only found this post whilst trying to find the very docs for the REST API that I used all those months ago - which appears to have disappeared SMH.
