Sum total occurrences of each value in a string using Power Query

[automated_user]

Hello, 

I was wondering if there is a way to sum the total occurrences of each value appearing in a string in a column called PseudoNo as show below:

As an example for the one highlighted in gray the output would be: 

PartNums	Count
TS12	2
TS12U2	2
TS24IC10	1

Any help would be great!

Thank you

[jennratten]

Does the expected result for the last two rows match what is shown below?  If not, please explain how the results for each row should be combined. 

PartsNums	Count
TS12	1
TS36IC10	1
TSD12BLW	1
TS24	1
TS12	2
TS12U2	2
TS24IC10	1

[automated_user]

Hello @jennratten 

So for each specific row, I have to also multiply the count of each part from the PseudoNo column with the shipping quantity of that row. For example the shipping qty column below with the PseudoNo column I have above:

Would result in the table for the highlighted rows:

PartNums	Count	TotalQty
TS12	2	4
TS12U2	2	4
TS24IC10	1	2

So the method that you stated above would work if we also added another column like above to track the total qty as well. 

[v-jingzhang · Accepted solution]

Hi @automated_user 

I assume that you have a column which contains unique values in the table e.g. OrderID, SellingID.... If not, add an Index column to the table first. 

Select PseudoNo column, split it by delimiter into Rows. 

Group by the Index column (or the unique key column) and PseudoNo column like below. 

At last add a custom column to multiply two columns. 

Sample file is attached at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.