Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

1 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

View solution in original post

4 REPLIES 4
bertuse2015
New Member

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.

v-piga-msft
Resident Rockstar
Resident Rockstar

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors