Forum Discussion
Avoiding Duplicate counts question
I have the below table and would like to visualize the following:
- To calculate the number of unique Technical IDs in relation to Process A and Process B.
- For example, there are 6 SAS 234s in relation to Process B however, Power BI will visualize that there are only 2 SAS 234s since the others have other values in the cell (SAS 234, SAS 567, etc.)
- I can't get Power BI to take the values in the cell that are separated by commas.
- Do I nned to remove the commas and create a separate column?
Any idea??
| Product ID | Company | Nature of Business | Process A | Process B | Technical ID |
| 325 | ABC | Retail | 0 | 1 | SAS 234 |
| 246 | XYZ | Oil & Gas | 0 | 1 | SAS 234, SAS 567 |
| 100 | 123 | Manufacturing | 0 | 1 | SAS 234, SAS 230, SAS 205 |
| 112 | 456 | Insurance | 1 | 0 | SAS 234 |
| 102 | ABC | Banking | 0 | 1 | SAS 220 |
| 325 | XYZ | Retail | 0 | 1 | SAS 220 |
| 246 | 123 | Oil & Gas | 0 | 1 | SAS 234 |
| 100 | ABC | Manufacturing | 0 | 1 | SAS 234, SAS 567 |
| 112 | XYZ | Insurance | 0 | 1 | SAS 234, SAS 230, SAS 205 |
| 102 | 123 | Banking | 1 | 0 | SAS 234 |
| 100 | 456 | Oil & Gas | 1 | 0 | SAS 220 |
Hi Tech325
In Power query, add a custom column below
Then modify measure
Measure = CALCULATE(COUNT(Query1[Process]),FILTER(Query1,[Text After Delimiter]=BLANK())) Measure 2 = SUMX(ALL(Query1[Nature of Business]),[Measure])Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
Best course would be to extract by delimetere in PowerQuery.
Add Columns > extract text before delimiter > , (this is for first code before ,)
second time > extract text before delimiter but go to advanced and skip 1 delimiter
Third time > skip 2 delimiter
After that go to transform tab and extract the codes you need from the new columns.
Be sure to use "," as a delimiter and make as many columns as you need. I figure from your data you won't have 1000 of Tech ID's on a single Product ID. so this won't be a problem to modify and it won't cause much problems. After that take the columns and unpivot them, remove blanks and you should be good.
EDIT: don't forget to clean and trim the new unpivoted column!- I would recommend splitting Technical ID column by comma in Power Query. Then unpivot the resulting columns. Should be pretty easy after that.
- Thanks for the response. Very helpful. I was able to use the delimit function and the unpivot function and got this: The issue I have is the following:
Since I'd like to visualize that for Company 123 there is 1 Process B that has 3 Technical IDs, if I try to visualize what I just did, the graph will show that there are 3 Process Bs with 3 Technical IDs.BEFORE:Product ID Company Nature of Business Process A Process B Technical ID Technical ID 2 Technical ID 3 325 ABC Retail 0 1 SAS 234 246 XYZ Oil & Gas 0 1 SAS 234 SAS 567 100 123 Manufacturing 0 1 SAS 234 SAS 230 SAS 205 112 456 Insurance 1 0 SAS 234 102 ABC Banking 0 1 SAS 220 325 XYZ Retail 0 1 SAS 220 246 123 Oil & Gas 0 1 SAS 234 100 ABC Manufacturing 0 1 SAS 234 SAS 567 112 XYZ Insurance 0 1 SAS 234 SAS 230 SAS 205 102 123 Banking 1 0 SAS 234 100 456 Oil & Gas 1 0 SAS 220 AFTER:Product ID Company Nature of Business Process A Process B Attribute Value 325 ABC Retail 0 1 Technical ID SAS 234 246 XYZ Oil & Gas 0 1 Technical ID SAS 234 246 XYZ Oil & Gas 0 1 Technical ID 2 SAS 567 100 123 Manufacturing 0 1 Technical ID SAS 234 100 123 Manufacturing 0 1 Technical ID 2 SAS 230 100 123 Manufacturing 0 1 Technical ID 3 SAS 205 112 456 Insurance 1 0 Technical ID SAS 234 102 ABC Banking 0 1 Technical ID SAS 220 325 XYZ Retail 0 1 Technical ID SAS 220 246 123 Oil & Gas 0 1 Technical ID SAS 234 100 ABC Manufacturing 0 1 Technical ID SAS 234 100 ABC Manufacturing 0 1 Technical ID 2 SAS 567 112 XYZ Insurance 0 1 Technical ID SAS 234 112 XYZ Insurance 0 1 Technical ID 2 SAS 230 112 XYZ Insurance 0 1 Technical ID 3 SAS 205 102 123 Banking 1 0 Technical ID SAS 234 100 456 Oil & Gas 1 0 Technical ID SAS 220 Question: How can I make sure that I can visualize that Company 123 has 1 Process B with 3 Technical IDs?Thanks!Unpivot Process A and B. This will merge them together into a column that can just be called "Processes".
Then you can put a table in the visual with Process and Technical ID. Add a slicer that lists companies.
Now you can click on a company in a slicer and see the filtered information. 🙂
You can't put this in any kind of chart since it has no numerical value so it doesn't show anything.
