Forum Discussion

Tech325's avatar
Tech325
New Member
6 years ago
Solved

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 rela...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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!