Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to transpose or unpivot text values into separate columns based on relative date. My data looks like this:
Unique ID | Date | Comment |
1 | 7/1/2019 | aaaaa |
1 | 8/1/2019 | bbbbb |
1 | 9/1/2019 | ccccc |
2 | 7/1/2019 | ddddd |
2 | 8/1/2019 | eeeee |
2 | 9/1/2019 | fffff |
3 | 7/1/2019 | ggggg |
3 | 8/1/2019 | hhhhh |
3 | 9/1/2019 | iiiii |
I need to create separate columns that return the most recent, 2nd most recent, third most recent values in the Comment column based on the Date field per Unique ID. My end result would look something like this:
Unique ID | Most Recent Comment | 2nd Most Recent Comment | Third Most Recent Comment |
1 | ccccc | bbbbb | aaaaa |
2 | fffff | eeeee | ddddd |
3 | iiiii | hhhhh | ggggg |
I realize that the easiest way to address this is with the Query Editor, but unique circumstances require me to use DAX and calculated columns only.
How would I go about creating these calculated columns?
Solved! Go to Solution.
Hi @TimQ
Did you try Matrix?
Hi @TimQ
Try below M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczLCYAwEIThXuYciBsPmlrCHnxr/w3oRNhs/tPAB1MKBAFTlJgGyd9cGDT8MDdYmUFusLEKqb/amYG7OpiBuzpZhbG/upiBu7qZgbt6GFRf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Date = _t, Comment = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", Int64.Type}, {"Date", type date}, {"Comment", type text}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US")[Date]), "Date", "Comment") in #"Pivoted Column"
Pbix attached.
Thanks. This solution seems like it would work but due to a weird network circumstance I am unable to transform my data table in the query editor, so my solution would have to be executed as a series of DAX calculated columns in a visual.
Hi @TimQ
Did you try Matrix?
User | Count |
---|---|
94 | |
89 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
107 | |
88 | |
64 | |
63 |