Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
can any body help me with the below ask :
appreciated in advance!!
Source:
Date | hosp_volume | phy_volume | overall_volume | |||||||||||||||||||||
2/1/2024 | 1234 | 111 | 1345 | |||||||||||||||||||||
2/2/2024 | 10158 | 56308 | 66466 | |||||||||||||||||||||
2/3/2024 | 22349 | 42626 | 64975 | |||||||||||||||||||||
2/4/2024 | 25045 | 30501 | 55546 |
Output in powerBI
|
Solved! Go to Solution.
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.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.