Forum Discussion
pivot table equation in powerbi
Select your 3 columns you want as rows > right click > unpivot columns
Select Date column > Transform > Pivot column > set 'Value' as the values column
Done!
Hi,
Thanks for the solution Syk provided, it is great, and i want to offer some supplement for user to refer to.
hello ganeshkandi , based on the solution Syk provided, if you update the date , you can try to click the date column, then select unpivot other columns, then the other steps please follow the step Syk provided, then if your date format is right after updating , it will display the data correctly.
You can also refer to the following M code in advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDcAwCEPRXThHChjiNrNE2X+NQNX24n958lqCHh2KkCYGf2JW6zFktwL4gdq4s4OuVTLIF/mHkC8zGyBYKOaVT/sA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, hosp_volume = _t, phy_volume = _t, overall_volume = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"hosp_volume", Int64.Type}, {"phy_volume", Int64.Type}, {"overall_volume", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US")[Date]), "Date", "Value") in #"Pivoted Column"If the solutions Syk and i offered help you, please consider to mark them as a solution.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Select your 3 columns you want as rows > right click > unpivot columns
Select Date column > Transform > Pivot column > set 'Value' as the values column
Done!
We appreciate your time, and please note that these date values are subject to change every day. so more dates will arrive and complement the information.
Should that be the case, will the updated data be incorporated into the table visualization? or will it produce any errors for us?
due to the fact that the column list does not contain the updated date.
The data should be updated with no errors! However, you may want to reconsider this approach because dates as columns gets hairy really quickly. I'm not sure what your trying to achieve, just a note.
Hi,
Thanks for the solution Syk provided, it is great, and i want to offer some supplement for user to refer to.
hello ganeshkandi , based on the solution Syk provided, if you update the date , you can try to click the date column, then select unpivot other columns, then the other steps please follow the step Syk provided, then if your date format is right after updating , it will display the data correctly.
You can also refer to the following M code in advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDcAwCEPRXThHChjiNrNE2X+NQNX24n958lqCHh2KkCYGf2JW6zFktwL4gdq4s4OuVTLIF/mHkC8zGyBYKOaVT/sA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, hosp_volume = _t, phy_volume = _t, overall_volume = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"hosp_volume", Int64.Type}, {"phy_volume", Int64.Type}, {"overall_volume", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type text}}, "en-US")[Date]), "Date", "Value") in #"Pivoted Column"If the solutions Syk and i offered help you, please consider to mark them as a solution.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
