Forum Discussion
Sum total occurrences of each value in a string using Power Query
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
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.
3 Replies
- jennratten
Super User
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 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-jingzhangCommunity Support
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.
