cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bryantmikd3 Frequent Visitor
Frequent Visitor

Convert Text to Number using Direct Query

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

3 REPLIES 3
epappu Frequent Visitor
Frequent Visitor

Re: Convert Text to Number using Direct Query

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

bryantmikd3 Frequent Visitor
Frequent Visitor

Re: Convert Text to Number using Direct Query

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

 

epappu Frequent Visitor
Frequent Visitor

Re: Convert Text to Number using Direct Query

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,541 guests
Please welcome our newest community members: