Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
decyp_tas
New Member

Delimiting and then combining items

Hello, I'm trying to create a visual from a column where there were a combination multiple items and single entry items. I want to show the sum of each of the items.

decyp_tas_0-1674005594482.png

I can spllit the items using a delimiter but when I try to make a visual it treats the items where there were multiple entries as separate to the single entry items of the same name (e.g. Safety has two separate bars (1 and 2) instead of a total bar (with a total of 3):

decyp_tas_1-1674005709931.png

Can you help me to filter, slice or delimit in a way that gives totals for each like-named item please?

 

1 ACCEPTED SOLUTION
SamInogic
Super User
Super User

Hi @decyp_tas,

As per your requirement, you have a report with table including Individual Support column where data is added using comma separated values. So, Power BI allows option to split the column values into either multiple columns or rows.

For your requirement it seems that you need to split the values into separate rows so that visual will show proper data in the report.

Therefore you can follow below steps in your power bi report to spilt up the data from Individual Support column

1.       Navigate to Power Query Editor and select the column “Individual Support” as shown in the below screenshot.

SamInogic_0-1674022645830.png

2.       Within Home section, select Split the Column and option will be “By delimiter” as shown in the below screenshot.

SamInogic_1-1674022667079.png

3.       This will open the Split Column by Delimiter dialog, so we have to select below listed options:

a.       Select or enter delimiter – Custom with value as “, “

b.       Split at – Each Occurrence of the delimiter

c.       Advanced options – Rows

Please refer to the below screenshot,
 

SamInogic_2-1674022689208.png

4.       This will split your column values into rows with delimiter as Comma as shown in the below screenshot,

SamInogic_3-1674022708169.png

5.       This can have multiple same entries (for example in above example Behaviour have 2 separate entries), so we can group it by Individual Support column so that it will sum up the same rows and have distinct rows in the table.  Click on Group By option

SamInogic_7-1674022779634.png

 

SamInogic_6-1674022749505.png


If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

View solution in original post

4 REPLIES 4
SamInogic
Super User
Super User

Hi @decyp_tas,

As per your requirement, you have a report with table including Individual Support column where data is added using comma separated values. So, Power BI allows option to split the column values into either multiple columns or rows.

For your requirement it seems that you need to split the values into separate rows so that visual will show proper data in the report.

Therefore you can follow below steps in your power bi report to spilt up the data from Individual Support column

1.       Navigate to Power Query Editor and select the column “Individual Support” as shown in the below screenshot.

SamInogic_0-1674022645830.png

2.       Within Home section, select Split the Column and option will be “By delimiter” as shown in the below screenshot.

SamInogic_1-1674022667079.png

3.       This will open the Split Column by Delimiter dialog, so we have to select below listed options:

a.       Select or enter delimiter – Custom with value as “, “

b.       Split at – Each Occurrence of the delimiter

c.       Advanced options – Rows

Please refer to the below screenshot,
 

SamInogic_2-1674022689208.png

4.       This will split your column values into rows with delimiter as Comma as shown in the below screenshot,

SamInogic_3-1674022708169.png

5.       This can have multiple same entries (for example in above example Behaviour have 2 separate entries), so we can group it by Individual Support column so that it will sum up the same rows and have distinct rows in the table.  Click on Group By option

SamInogic_7-1674022779634.png

 

SamInogic_6-1674022749505.png


If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Hello @SamInogic ,
I'm very grateful for your support and I feel like I'm now very close. When I group the delimited column I get this:

decyp_tas_1-1674078588154.png

I must be doing something wrong and wonder if you can help me?

 

 

 

Hi @decyp_tas,

Is it possible to share a Grouping Expression or Sample pbix file so that I can check the error message and we can fix the error further?

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Thank you @SamInogic ...you were correct in the first instance. I had failed to swap the delimiter from the default comma to a custom a custom ", " (as you had outlined).

I'm grateful for your guidance. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors