Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table like the one below. Each document "type" can have a specific set of comments. I want to be able to find out if Person = "Ric" && Comments Contains "Skill1" is TRUE or FALSE. Specifically, I just want to lookup a person and see if a particular strings exists in ANY occurence of the comment field for that person. For reference, my real table is named "DocList".
Person | Document | Comment |
Ric | Amateur | Skill1 Skill2 |
John | Amateur | Skill1 |
Sam | Amateur | Skill1 Skill2 Skill3 |
John | Pro | Ability1 |
Sam | Pro | Ability1 Ability2 |
John | Master | Color1 Color2 |
Thanks for your help.
Solved! Go to Solution.
Hi @RicFischer - In this situation, I would use Power Query to Text.Split and "Unpivot Other Columns". This would convert you example table to the following:
Person | Document | Comment |
Ric | Amateur | Skill1 |
Ric | Amateur | Skill2 |
John | Amateur | Skill1 |
Sam | Amateur | Skill1 |
Sam | Amateur | Skill2 |
Sam | Amateur | Skill3 |
John | Pro | Ability1 |
Sam | Pro | Ability1 |
Sam | Pro | Ability2 |
John | Master | Color1 |
John | Master | Color2 |
You will find it easier to perform the conditional measure and also to group by and slicer by the "Comment".
I'm open to suggestions. I have thousands of rows of data. All persons will appear multiple times and some people may have multiple occurences of the same document.
I would prefer having a separate table that lists each person once and has a bunch of true/false columns named Skill1, Skill2, Ability1, Ability2, Color1, Color2, etc. In that table, I wouldn't care about the document itself because each of the Comment items are separate regardless of the document.
The reason I'd like a table is for reusbility in other reports.
Hi @RicFischer - In this situation, I would use Power Query to Text.Split and "Unpivot Other Columns". This would convert you example table to the following:
Person | Document | Comment |
Ric | Amateur | Skill1 |
Ric | Amateur | Skill2 |
John | Amateur | Skill1 |
Sam | Amateur | Skill1 |
Sam | Amateur | Skill2 |
Sam | Amateur | Skill3 |
John | Pro | Ability1 |
Sam | Pro | Ability1 |
Sam | Pro | Ability2 |
John | Master | Color1 |
John | Master | Color2 |
You will find it easier to perform the conditional measure and also to group by and slicer by the "Comment".
can you demontrate what you what to end up with?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |