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.
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?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |