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

[Anonymous]

Hi all,

I have a collection of data that I am trying to adjust to break cumulative numbers down into specific period numbers. To this extent I've written the following code:

#"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})

The concept is that the column should compare both Columns 1 & 2 and if they both match, meaning they are part of the same data group, the Specific value should be the difference in amounts. If they don't match, meaning we're starting a new data group, then the Specific value is just the same as the current.

However, when I hit enter, the Specific column is filled with errors that say Expression.Error: We cannot convert the value "(text from Column1)" to type List.

Both Column1 and Column2 are type:text, and I have no idea why they would be converting the individual values to type List anyway. Can somebody help?

Thanks,

Jason

[bertuse2015]

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"

[DubiousTrout]

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.

[v-piga-msft]

Hi @Anonymous ,

To understand your scenario better, please the data sample which could reproduce your scenario and your desired output so that we could help further on it. Or, ideally, share the pbix (beware of confidential data).

Best Regards,

Cherry

[Nolock · Accepted solution]

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