An argument of function 'LEFT' has the wrong data type or has an invalid value.

[Risen]

Hi,

I have data comes as a string in the following format below:

540-1019;1980-2459;3420-3899;4860-5339;6300-6779;7740-8219;9180-9659

Basically these numbers are based on the formula below:

9 AM on Monday = 9 * 60 minutes = 540
5 PM on Monday = (9 * 60 minutes) + [(8 * 60 mins) - 1] = 1019
9 AM on Tuesday = 33 * 60 mins = 1980 5 PM on Tuesday = (33 * 60 mins) + [(8 * 60 mins) - 1] = 2459
9 AM on Wednesday = (57 * 60 mins) = 3420
5 PM on Wednesday = (57 * 60 mins) + [(8 * 60 mins) - 1] = 3899

Monday 9 AM – 5 PM = 540-1019
Tuesday 9 AM – 5 PM = 1980-2459
Wednesday 9 AM – 5 PM = 3420-3899
Thursday 9 AM – 5 PM = 4860-5339
Friday 9 AM – 5 PM = 6300-6779
Saturday 9 AM – 5 PM = 7740-8219
Sunday 9 AM – 5 PM = 9180-9659

These are the date ranges:
Mon = 0 - 1439
Tue = 1440 - 2879
Wed = 2880 - 4319
Thr = 4321 - 5759
Fri = 5760 - 7199
Sat = 7200 - 8639
Sun = 8640 - 10079

I wrote this in DAX in Power Bi and getting this error 😞

Can someone take a look what I am doing wrong?
I might wrote whole formula wrong. If you guys have any other advice that would be great too 🙂
All I want is to convert this data above to these dates on another column.
Thanks a lot in advance.

[ValtteriN]

Hi,

The issue is likely with your FIND function. When FIND can't find the value (e.g. if the row value is blank) this kind of error can appear within LEFT. E.g.

To combat this you can for example use IF or IFERROR:

Column = LEFT('Table (15)'[Column1],IF(ISBLANK(FIND("1",'Table (15)'[Column1],1,-1)),FIND("1",'Table (15)'[Column1],1,-1),1))

Here I am checking for blank value in the IF ^^

As a sidenote try utilizing parameters in your dax for the parts you are re-using a lot. E.g. 

Measure = 
param = Var FIND(logic here) return

LEFT(table[column],param)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/