Forum Discussion
Helper VImport Models and limitations on casting values in new columns
I have an imported Semantic model and I was attempting to add a column to a table that involves extracting a text representation of a data and recasting as an actual date column. However every approach I tried resulted in an error that basically indicates the text values are not being recast as numerical values so can't be converted. However, when I convert the same code to a measure it works fine. The drawback of course as a measure is that I have to use MAX or MIN to 'aggregate' the values per row, and it means I'll have to set-up some scope handling for conditions where the column happens to be truly aggregagetted (the most likely 'risk' is matrices in this particular case).
Is it correct to assunem this is a limitation of the Import Model process?
grrr, bad data strikes again. Some more digging and playing with measures and some bad text strings showed up. Have now accounted for that in my logic which will hopefully put the issue to rest, but there is only so much you can do when dealing with an open string field that a user can enter anything they want to.... 😞
8 Replies
- OwenAuger
Super User
Hi rpiboy_1
Can you share some more detail on the text representations that you want to convert to date, and where in the process you are attempting the conversion?
If you are handling the conversion in Power Query, the Date.FromText function may be useful.
For example:
Date.FromText("30 Dez 2010", [Format="dd MMM yyyy", Culture="de-DE"]) Date.FromText("10-Jan-2025", [Format="d-MMM-yyyy])You could also handle conversions in a DAX calculated column but Power Query would be preferable.
rpiboy_1That is the thing, it's an imported published model so I can't manipulate the data is Power Query and the need doesn't justify going back and modifying that model for this very specific reporting requirement. So I'm left with what I can do with DAX and as I said while I can create additional custom columns, it does not like attempting to cast the string as a numerical value.
- OwenAuger
Could you show some examples (screenshots/code) of where you're trying to convert to date with DAX?
One way or another, the conversion should be possible.
- rpiboy_1
grrr, bad data strikes again. Some more digging and playing with measures and some bad text strings showed up. Have now accounted for that in my logic which will hopefully put the issue to rest, but there is only so much you can do when dealing with an open string field that a user can enter anything they want to.... 😞
- OwenAuger
Glad to hear it 🙂
I was just looking at this and managed to reproduce the issue.From the error message, you have a DirectQuery connect to an existing Power BI semantic model and are adding a calculated column in the local model?
There must have been a column containing "Closed" and ending in "irection" (among the other issues you've found I'm sure) 🙂
- rpiboy_1
Yeah, it was such gibberish in the error message, I didn't realize it was bad text strings and I had not spotted them during previous manual scans of the data. Once it showed up when I started taking a different approach that was purely text based, it was pretty easy to back check and find the 'bad' data.
