pivot table equation in powerbi

[ganeshkandi]

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 Date 2/1/2024 2/2/2024 2/3/2024 2/4/2024 hosp_volume 1234 10158 22349 25045 phy_volume 111 56308 42626 30501 overall_volume 1345 66466 64975 55546

[Syk · Accepted 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!

[Syk · Accepted 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!

[ganeshkandi]

@Syk 

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.

[Anonymous · Accepted solution]

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.

[Syk]

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.