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 tried for a while to solve this but can't work it out. A lot of the suggestions on here are similar but when I've followed solutions it never quite worked out and it's ended up duplicating my rows.
I have a table called Tickets which contains support ticket numbers. Each ticket number has an ID column. This column and table is linked to another one called Dynamic Fields Values. The link to the Ticket table is on a column called object_id and in this there are multiple duplicate entries linking to the tickets. For each object_ID there is a column called dynamic_field.name and a corresponding column called value_text. This mean that for example object_id 4700 could have a dynamic_field.name of 'Source' with the value_text 'Email' and then in another row for 4700 a dynamic_field.name of 'OverSLA' with the value_text 'Yes'.
What I want is one row for 4700 with a column for Source and a column for OverSLA. Is this possible? nothing I've tried has worked so far and when I try to do a join between Tickets and DynamicFields I end up with duplicate ticket numbers. Please help!
Solved! Go to Solution.
Hi @Anonymous
Only select this column "dynamic_field.name", then pivot column
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.
Hi,
This is the current table structure.
object_id | value_text | dynamic_field.name |
4762 | Phone | Source |
4762 | 2 medium | ITSMCriticality |
4762 | 3 low | ITSMImpact |
4762 | 3rd Party Referral | ClosureActions |
35934 | No | ITSMReviewRequired |
35934 | Complex | Screen |
35934 | 0012000000xb1sC | SalesID |
35934 | Phone | Source |
I need to separate out the dynamic_field.names into new columns and then the add the corresponding value_text for each grouped object_id. I need to do this so I have a one to one relationship between the object_id and another table with the ticket data.
object_id | Source | ITSMCriticality | ITSMImpact | ClosureActions | ITSMReviewRequired | Screen | SalesID |
4762 | 2 medium | 3 low | 3rd Party Referral | ||||
35934 | Phone | No | Complex | 0012000000xb1sC |
As added background the Ticket table is the main one I'm reporting on so I would like to be able to create reports for the dynamic fields for each ticket. I can do this once I manage to get my table to look like the one above.
Make sense?
Hi,
That's great and solves one part but then I need to group by object_id. I tried that but end up with the below. When I expand count to show the rows it just ungroups the object_id again.
Thanks,
Tom
Hi @Anonymous
Only select this column "dynamic_field.name", then pivot column
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.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |