Import Models and limitations on casting values in new columns

[rpiboy_1]

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?

[rpiboy_1 · Accepted solution]

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.

[OwenAuger]

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_1]

That 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]

Yes, I can tomorrow. 

[rpiboy_1]

First here is the error message I get when attempting to define a new column w/DAX.

Here is the measure code, broken out for testing/analysis and ititerative construction. Worth noting that this is the most recent iteration. I've also tried casting each unique string component as a number, and then attempting to use the DATE function with no success and a similiar error. It all seems to be point to not being able to convert the text strings into numerical representations.

Closed Date = 
VAR _isClosed = CONTAINSSTRING('Projects'[contract_ref], "Closed")

VAR _result =
    IF(
        _isClosed,
        RIGHT('Projects'[contract_ref], 8),
        BLANK()
    )

VAR _year =
    IF(
        _isClosed,
        LEFT(_result, 4),
        BLANK()
    )

VAR _month =
    IF(
        _isClosed,
        MID(_result, 5, 2),
        BLANK()
    )

VAR _day =
    IF(
        _isClosed,
        RIGHT(_result, 2),
        BLANK()
    )

VAR _finalResult =
    IF(
        _isClosed,
        DATEVALUE(_month & "-" & _day & "-" & _year),
        BLANK()
    )


RETURN
_finalResult