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 folks, I'm hoping someone can help. I have a data table that contains rows of IT support tickets, the Assignment Groups that the tickets were transferred to (one 'transfer activity' per row), and the timestamp of when the ticket transfer happened. An example is below:
Number | Transfer | Created |
INC0184914 | Business Systems | 02/04/2020 15:14:40 |
INC0184914 | Business Systems | 30/03/2020 11:31:45 |
INC0184914 | Info Apps | 18/02/2020 15:24:16 |
INC0184914 | Info Apps | 04/02/2020 16:06:17 |
INC0184914 | Infrastructure Team | 30/03/2020 13:46:50 |
INC0173401 | Infrastructure Team | 17/02/2020 10:37:27 |
INC0200144 | Service Site 1 | 20/02/2020 10:04:25 |
INC0200144 | Service Site 2 | 07/02/2020 14:03:45 |
INC0173401 | Service Site 3 | 12/02/2020 11:33:20 |
INC0200144 | Service Site 6 | 04/02/2020 15:27:37 |
(the live data is in a random order in this table)
I'd like to transform this data into a virtual table so that:
Number | Transfer 1 | Transfer 2 | Transfer 3 | Transfer 4 | Transfer 5 |
INC0200144 | Service Site 6 | Info Apps | Service Site 2 | Info Apps | Service Site 1 |
INC0184914 | Info Apps | Info Apps | Business Systems | Infrastructure Team | Business Systems |
INC0173401 | Service Site 3 | Infrastructure Team |
The number of 'transfer' columns would need to expand and contract, depending on the maximum number of ticket transfers.
Ideally I'd like to be able to export data from this virtual table into a .CSV in the same format as the example result table above.
Any help would really be appreciated 🙂
Solved! Go to Solution.
@MichaelHutchens add new column using following DAX expression:
Rank Transfer =
RANKX (
FILTER (
'Table',
'Table'[Number] = EARLIER ( 'Table'[Number] )
),
'Table'[Created] , ,
ASC,
Dense
)
Add a matrix visual, user number on rows, rank transfer on columns, and Transfer on values
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Ashish_Mathur This is great but I will not go that route. Pivot is a very expensive query and it will take forever to work. Cheers!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you for the feedback @parry2k
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZCxDoIwFEV/pWGmyXt9FbCbOrG44EYYCKkJA0hoMfHvbRMFBAxrc0/PfTfPg/R6AUzkEWUQBufB1K02hmUvY3Vj3BMIDpILEMDwoFyqCPchAg70gVARrqG0vT/Yqet8GhPuLF+F2FDM067NmI4URJvpvjS2Hyo79JrddNksOpGSMy4mCfiXw3jygaJ45AQASu/LdP+sK82y2mrmPxIwR0DuIMJfNbNIBbRu94OQLyYmxI1MO5ZosZ1b2t1SvAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Transfer = _t, Created = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type text}, {"Transfer", type text}, {"Created", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Number", Order.Ascending}, {"Created", Order.Ascending}}),
Partition = Table.Group(#"Sorted Rows", {"Number"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Transfer", "Created", "Index"}, {"Transfer", "Created", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each "Transfer "&Number.ToText([Index])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Created"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Transfer")
in
#"Pivoted Column"
Hope this helps.
@MichaelHutchens glad, I could help. Good luck!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@MichaelHutchens add new column using following DAX expression:
Rank Transfer =
RANKX (
FILTER (
'Table',
'Table'[Number] = EARLIER ( 'Table'[Number] )
),
'Table'[Created] , ,
ASC,
Dense
)
Add a matrix visual, user number on rows, rank transfer on columns, and Transfer on values
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k, thanks so much for this. Aside from being able to export the results into .CSV (and that is a 'nice to have'), this fits the bill really well 🙂
@MichaelHutchens why INC0184914 will not show "Info Apps"?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k , apologies that was my error. I've edited the original post to correct this now 🙂
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |