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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors