Split and tally data in a delimited column

[mmarsala]

Hi all,

I have a set of data I'm pulling in from an Azure table that has a column of semi-colon delimited text with counts like this:

amd64=3;

UNKNOWN=2;amd64=2;

x86=2;amd64=1;UNKNOWN=1

amd64=5;

x86=3;

UNKNOWN=2;

I would like to create a table on one of my dashboards with totals like this:

amd64	11
UNKNOWN	5
x86	5

I'm new to PowerBI and have been struggling with various splits, and even R scripts. Is there a simple series of transformations that you can recommend to help me get this output? As a note, while the example here only has a max of three items delimited by a semi-colon, the data I receive from the Azure table could have more.

Thanks for your help!

-Matt

[HotChilli · Accepted solution]

Split the column by delimiter (semicolon) using the Advanced option "To Rows".

Filter out the blank rows.

This gives a single column table.

Split the column by delimiter (=)

This gives 2 columns.

Then use 'Group By' to group on the first column (the text) with the Sum of the second column.

Make sure the columns have the correct datatype at each stage.