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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kreg
Helper I
Helper I

Query does not work as expected

Hi,

 

I have a query which works partially:

Model Type =
 IF (LEFT(model[type], 8) = "https://" ||
 OR(
    ISNUMBER(model[type])
    ,0
    ),
    "Accepted","Failed")
 
Now, you can imagine that someone provide a full link to car model field which is a "type" i.e. https://tesla.com/345345 or only 345345 and both of that inputs should be treat aa Accepted, but now only hyperlink goes to the Accepted bucket, but number - based on the above query - still to the Failed bucket. It is only provided in the first place, so I thought that part with ISNUMBER solves that issue, but not. How should I change it?




1 ACCEPTED SOLUTION

@Kreg ,

Here is a slight modification:

 

 

Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

 

 

Type                                       Accepted/Failed

https://tesla.com/345345 Accepted
345345 Accepted
- Failed
Not Applicable Failed

The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

Regards,

View solution in original post

4 REPLIES 4
rsbin
Super User
Super User

@Kreg ,

Looks like there are a few issues with your syntax.  Please try this:

 

Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8) = "https://", "Accepted",
                      ISNUMBER( VALUE([Type] )) = TRUE(), "Accepted",
                      "Failed" )

 

 

rsbin_0-1685130611427.png

I trust I interpreted your question correctly.

Regards,

You interpreted it very correctly but I missed that in some rows might be some string provided by the user like "-", "Not Applicable", etc. and when I used your query I got message:
"Cannot convert value '-' of type Text to type Number."

So lets say that everything those kind of input must be treated as Failed. 

@Kreg ,

Here is a slight modification:

 

 

Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

 

 

Type                                       Accepted/Failed

https://tesla.com/345345 Accepted
345345 Accepted
- Failed
Not Applicable Failed

The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

Regards,

Thats IN really works and looks very smart. Thanks for that. It works for me.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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