cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

LEFT function doesn't accept a calculated 2nd parameter value

Datetime2 = VAR Length = INT(LEN('Verizon Wireless'[Datetime]) - 5)
    RETURN LEFT('Verizon Wireless'[Datetime], Length) & ":" & 
RIGHT('Verizon Wireless'[Datetime], 5)

 

I'm importing CSV data that comes from Verizon Wireless. PBI is mangling their Time column so I'm having to do a few steps to parse it out manually (most of these steps are not shown).

 

The above column definition is my final step in this process. Once this column can be created I expect I should be able to convert the type of the column from Text to Datatime.

 

The problem is that LEFT() won't accept a 2nd parameter that's been dynamically calculated. If I type a value in, it works fine. The problem is that this is not possible because the left portion I want is a variable length.

 

I also tried running the calculation through INT() thinking that might solve it but I had the same problem.

 

Here's a screenshot of the formula, data, and error message. What am I doing wrong here?

 

Lastly, using the first record as an example, the output in Datetime2 would be "12/22/2017 3:19 PM".

 

Screenshot

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III
Helper III

Re: LEFT function doesn't accept a calculated 2nd parameter value

As is usually the case, I found the answer a few minutes after finally giving up and posting my question in a forum.

 

Not every column in Datetime has data so LEN() is returning an error on those columns. This has pointed out some other datetime shenanigans in Verizon's data so I'm going to have to take a different approach.

View solution in original post

1 REPLY 1
Highlighted
Helper III
Helper III

Re: LEFT function doesn't accept a calculated 2nd parameter value

As is usually the case, I found the answer a few minutes after finally giving up and posting my question in a forum.

 

Not every column in Datetime has data so LEN() is returning an error on those columns. This has pointed out some other datetime shenanigans in Verizon's data so I'm going to have to take a different approach.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors