Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Hoping someone may be able to provide some guidance on the following. I have two datasets: 1) listing personnel and their authorisations, 2) Products and the necessary authorisations.
I want to be able to cross reference the two such that i can find who is authorised for what product. I had an excel macro, created by an ex-collague, that could do this but it unfortunately broke, and would like to try do this via powerbi
Below is a simplied version of the Data:
Person | Authorisation |
Person 1 | A |
Person 2 | A |
Person 2 | B |
Person 2 | C |
Person 3 | B |
Person 3 | C |
Person 4 | A |
Person 4 | B |
Person 4 | D |
Product | Authorisation |
Product 1 | A |
Product 2 | B, C |
Product 3 | A, B, C |
Prduct 4 | A, D |
From here we can see that Person 1 is authorised for product 1, Person 2 is authorised for Product 1, 2, 3, Person 3 is authorised for Product 2, and Person 4 is authorised for Product 4.
Not sure if this helps but I am able to transform the 1st table such that you have unique rows for the Person column i.e.:
Person | Authorisation |
Person 1 | A |
Person 2 | A, B, C |
Person 3 | B, C |
Person 4 | A, B, D |
What i'm struggling is how to connect the 2 data sets. Any ideas would be gratelly appreciated.
Solved! Go to Solution.
Hi @Anonymous ,
I create a sample you can reference to modify yours.
Split the column by delimiter in Query Editor -- > Unpivot Columns -- > Remove Columns -- > Replace a space to none – > Close & Apply
Create a measure in report view
Measure = CONCATENATEX(FILTER(VALUES(Table2[Product ]),CALCULATE(ISEMPTY(EXCEPT(VALUES(Table2[Authorisation]),VALUES(Table1[Authorisation]))))),Table2[Product ],",")
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I create a sample you can reference to modify yours.
Split the column by delimiter in Query Editor -- > Unpivot Columns -- > Remove Columns -- > Replace a space to none – > Close & Apply
Create a measure in report view
Measure = CONCATENATEX(FILTER(VALUES(Table2[Product ]),CALCULATE(ISEMPTY(EXCEPT(VALUES(Table2[Authorisation]),VALUES(Table1[Authorisation]))))),Table2[Product ],",")
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-xuding-msft Is there any way we can do it in Power Query entirely? This operations looks like Fuzzy Merge, with CONTAINS a text, but I don't know how to operate it yet.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |