Convert Text to Number using Direct Query

[Anonymous]

Hello!

Looking for some help with a text column and Direct Query.  I have a column called "Talking" that is the total amount of time spent talking for a user.  The column in table is storing the data as text (for example, 04:57:00).  I'm strugling to get this converted to a number or duration via Direct Query.  I also struggle to convert once I bring into Desktop.  I'm ultimately trying to get an Average Talk time per call (I have another column that has total calls as a number).

Any ideas or help would be fantastic.

Thanks

Mike

[Anonymous]

Hi Mike - 

First off, as you know the data is treated as text because of the : in there.

1. If this needs to be done in DirectQuery itself then it needs to use some kind of Split function in the query, like STRING_SPLIT in SQL Server for example.

2. Alternatively you can get the data of "Talking" as Text to PowerBI perform the following steps in Modeling:

   a. Add a new column with the following code for hours:  hours = PATHITEM(SUBSTITUTE('Table'[Talking], ":", "|"), 1)
   b. Add another new column with the following code for minutes: mins = hours = PATHITEM(SUBSTITUTE('Table'[Talking], ":", "|"), 2)
   c. Then for seconds: hours = PATHITEM(SUBSTITUTE('Table'[Talking], ":", "|"), 3)

3. Once the above is completed you can change the data type for all the above columns into number
4. Once that is done, You can use the following to get overall seconds of talking in another new column:
    TotalSecsTalked = (('Table'[hours]*60)*60) + ('Table'[Mins] * 60) + 'Table'[secs]


If the above helped your situation, please help mention this as a solution, if not please let me know. Hope this helps.

Regards
Eswar

[Anonymous]

Hi Eswar

Thanks for the reply.  When creating the new column I'm getting the below error:

Function 'PATHITEM' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Not really sure where to go from here.

Thoughts?

Thanks

Mike

[Anonymous]

Hi Mike - 

Can you please share your file for me to look at?

By the way I am using the latest version of PowerBI Desktop for this, may I know what version are you using?

Regards

Eswar