Forum Discussion
OLE DB or ODBC error: [Expression.Error] We cannot convert the value true to type Text..
Hi All,
I have an error when I'm refreshing my PBI dashboard. I have an excel sheet source with 3 tabs in it, and each tab's data is taken as source for one table initially. In a later step, these 3 sources are appended. In this new table, I'm joining 2 columns of type text (unchanged from before) in a new column. I got the error in this step. However, there are no true/false values in this text column, but a few blank/null values.
My collegue refreshed the report with same source, and same report, and was able to do it successfully without any errors.
The only difference I have with my colleague is the order of the columns in our source file - which is in sharepoint. When I saved the data to the file from a different Clarity data extract, my columns' order is different. But per my understanding, Power BI has no issues if the order of the columns are different but the headers are available.
I am unable to understand why the issue occured to me. Hoping to get some answers here. Thank you!
Hi buddingpbidev
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
6 Replies
Power query is detecting a boolean value incorrectly.
Before the step that is causing issues, manually change both columns to text.
Another option would be to edit your step to use Text.From()
Text.Combine({Text.From([Column1]), Text.From([Column2])}, " ")This will ensure any boolean values or null are transformed to text.
--------------------------------
I hope this helps, please give kudos and mark as solved if it does!
Connect with me on LinkedIn.
Subscribe to my YouTube channel for Fabric/Power Platform related content!
My search on Google got me to this solution. But I'm curious because of 2 reasons.
1: There are no boolean values in the column, just a few blanks/nulls.
2: Even 1 couldn't be an issue because my colleague was able to refresh the same report with same source data without any errors.My initial thought was the order of the columns, but Power BI apparently shouldn't have any issue with how the columns' order is in the data if there are proper headers. So I'm stuck wondering what's the root cause.
One small caveat here is, the data extract from Clarity is in CSV. When my colleague opens it, it asks for "Don't convert", and "Convert" (can't remember what exactly that it is about). They click on "Don't convert" and then save the file in .xlsx format. However for me, I don't get that. I just get a warning, and it asks me to save as .xslx. This seems very trivial, but I thought I'd put this out there.It is strange that your colleague can refresh the data successfully, is your power bi desktop running on the latest version? If not, try updating, this may fix your issue. Your colleague may be up to date. Worth a shot.
Hi buddingpbidev ,
Thanks for reaching out to the Microsoft fabric community forum.
Thank you wardy912 for the valuable point. In addition to their point, please check this as well.Please address the blank value issue within your model first, as it may be causing the refresh to fail. Once the underlying data is corrected and the blanks are handled appropriately, try running the refresh again to see if it completes successfull
Please try this in Power Query:
= Table.ReplaceValue( #"Changed Type","null","blank",Replacer.ReplaceValue,{"Column1","text"} )I hope this information helps. Please do let us know if you have any further queries.
Thank youHi buddingpbidev
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
