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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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/

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors