Limits in formulas because of datatype Hi! I dont know if its only me having this problem but in excel I have never had difficulties creating advanced formulas with different types of data, for example making a sum (value) when I find a specific word (text). In Power BI I have set the datatype the same for several columns of which I need to create new calculated columns for in my report. But when trying to add a new formula column i get the error that the formula cant work with different datatypes (expressions that yield variant data-type cannot be used to define calculated columns). First of all I dont know if there is a background set that mess up the datatype though I have set the same format for all the data. Second it is possible to create the same formula I have made in excel, which works perfectly. I don't see the problem why it shouldn't work in Power BI?
5 Comments
- I'm afraid that the likelihood of this request being accepted is very low. M is a fairly strongly typed language by design. Strongly typed systems not support type coercion because it is dangerous and error prone. You must explicitly convert a number stored as text to a number, for example, if you want use the text number with other real numeric data.
- And I don't know which data it refers to either though it does not name the data that is supposed to have the wrong datatype
- But I believe there should be a difference between the Query editor language and the "new measure/New column" in the report view. I can understand if this blocks measueres in the query editor but if I have set all the data with the same datatype according to the "Data" view, it should not claim that the datatype is wrong when trying to create a simple formula for a new column or measure in the report view. If it does it should show me which value is wrong so that I can analyze why it's doing this, now I can't even understand why PBI is claiming that the datatype is wrong when it should not be.
- I've found that the typical problem is often in mixing the data types that are generated by the logical calculation will cause this error. Be sure to create consistent data type results in your formulas. I was evaluating bit columns and converting the result to a string but left the last logical check result equal to a zero. Changing it to "None" fixed the type error. Hope that helps.
- Status added:Needs Votes
