topic Re: Pivot rows to columns in DAX Commands and Tips

Pivot rows to columns

bharukc — Sat, 28 May 2022 15:39:12 GMT

My data is like below

id. Status. Status date

1. Active. 2/14/18

1. Approved 2/15/17

1. In review 2/19/18

1. Active. 3/15/19

my result

id active. Approved In review

1 2/14/18. 2/15/17. 2/19/18

1 3/15/19

Re: Pivot rows to columns

tamerj1 — Sat, 28 May 2022 15:47:25 GMT

Hi bharukc

use a matrix visual. Place the id in rows and status inn columns and measure: SELECTEDVALUE ( TableName[Date] ) in values

Re: Pivot rows to columns

bharukc — Sat, 28 May 2022 17:03:35 GMT

I need to create pivoted table so that I can make gantt chart to show how many days does it take for each milestone.

Re: Pivot rows to columns

Fowmy — Sat, 28 May 2022 17:19:56 GMT

bharukc

You can achieve this in Power Query.
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJMLsksSwUyjPQNTfQNLZRidaASBQVF+WWpKRApU31Dc7hUUWpZZmo5RMISRQ/MMGOwDkul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Status Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Status Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Status"}, {{"Count", each _, type table [ID=nullable number, Status=nullable text, Status Date=nullable date]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Status"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Table.AddIndexColumn( [Count],"Index",1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Status", "Status Date", "Index"}, {"ID", "Status", "Status Date", "Index"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"ID"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Status]), "Status", "Status Date") in #"Pivoted Column"

Re: Pivot rows to columns

tamerj1 — Sat, 28 May 2022 18:21:43 GMT

You don't have to. You can calculate that without having you table pivotted. However, I have to say it might be easier to do the calculation while the table is pivoted.

Re: Pivot rows to columns

bharukc — Mon, 06 Jun 2022 20:39:44 GMT

Hi Fowmy

how about if I want each status to be a column.
if there are two dates for Active status, i want two status column so that I can show a id went to active status status after an inactive status.