Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the below table and would like to visualize the following:
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 |
Solved! Go to Solution.
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.
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!
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 |
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 |
Hi @Tech325
I would value Sven_H's suggestion.
Unpivot "Process A" and "Process B", then filter the rows of "1" for the "value" column.
Add columns to the matrix.
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.
Thank you for the screenshots and the time. This has been very helpful. One thing I've noticed from your screenshot is that, for example, You have Company 123 with a count of 3 Process Bs when in reality it should only be 2 Process Bs. Any reason why this would be happening?
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 |
Hi @Tech325
Modified:
follow "unpivot columns" suggestion in my previous answer, keep data in this format:
Then create measures
Measure = DISTINCTCOUNT(Query1[Process])
Measure 2 = COUNTX(ALL(Query1[Nature of Business]),[Measure])
count of process = IF(ISINSCOPE(Query1[Nature of Business]),[Measure],[Measure 2])
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 |
Product ID | Company | Nature of Business | Process A | Process B | Technical ID | Technical ID 2 | Technical ID 3 |
325 | ABC | Banking | 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.
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.