Delimiting and then combining items

[decyp_tas]

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.

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):

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

[SamInogic · Accepted solution]

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.

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

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,
 

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

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

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/

[decyp_tas]

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:

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

SamInogic 

[SamInogic]

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/

[decyp_tas]

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.