cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antiparras Helper II
Helper II

Group codes and sum their values from a single text string

Hi,

 

I've got a text column with this format "Oper A - 8.5, Oper B - 3.0, Oper A - 2.5, Oper C - 5.0, Oper B - 5.0" to define the operations that happened in 24 hours. Basically time is separated by " - " from the operation code and each entry is separated by commas.

What I would like to have at the end is "Oper A - 11.0, Oper B - 8.0, Oper C - 5.0", meaning I would like to group the operation codes and sum their hours.

 

Anybody could suggest any approach to this? Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Group codes and sum their values from a single text string

Hi @Antiparras

 

You can split those into their own rows using the same split function.  Just click the advanced option in the Split dialog and set something like this for your column.

 

 

split to rows.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Microsoft Phil_Seamark
Microsoft

Re: Group codes and sum their values from a single text string

Hi @Antiparras

 

First of all, try splitting the column into two columns.  I suggest doing this in the Query Editor.

 

 

PQ1.png

Then group them in the Query Editor.  This should give you the output you are after.

 

PQ2.png

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Antiparras Helper II
Helper II

Re: Group codes and sum their values from a single text string

 

Thank you for your reply Phil,

 

 

That part is easy, but the data I've got is a bit more complex. As I mention in my original post, there're several codes in the same text column, separated by commas, so splitting the columns will result in a variable number of extra columns. To get more context, see the screenshot of the column I'm taking about.

 

Timecode3Example.png

 

The number of codes could be different per row, and the times too.

 

Thank you! 

 

 

Microsoft Phil_Seamark
Microsoft

Re: Group codes and sum their values from a single text string

Hi @Antiparras

 

You can split those into their own rows using the same split function.  Just click the advanced option in the Split dialog and set something like this for your column.

 

 

split to rows.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Antiparras Helper II
Helper II

Re: Group codes and sum their values from a single text string

Thank you @Phil_Seamark,

 

I think I had in my mind an easiest way to calculate "on-the-fly" (without creating extra columns) the values after the commas and group them by the operation codes. I'll try the splitting instead. Thank you again for your help!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors