Transpose Data: 365 rows/24 columns to 8670 rows/1 column

[WesCo_1906]

Hello!  I am a PowerBI Newbie.  I have a one year dataset of hourly monitored sensor data that is in the format of 365 rows (days) /24 columns (hours).  For purposes of a data visualization I'm developing, I would like to convert that dataset to a format of 8670 rows (hours)/1 column.

How can I use Power BI to elegantly and efficiently convert that dataset to the 1 column (year) x 8760 rows (hours) format?

[WesCo_1906]

Thank you!  As a follow up:

1) How do I maintain the sort order created by this transformation to use it for creating chart visualizations?  When I go back to the home view, I lose the sort order created by the transformation.

2) How can I add a column that includes a count for the rows of data in the unpivoted column?

[jdbuchanan71]

@WesCo_1906 

Your screen shot does not look like your example data.  I would have expected the Attribute column to be 1-24. If you convert it to an whole number you should be able to sort your visual by Date and Hour and it will sort correctly.  You can also add an index column in PowerQuery after you do the transformation if you want.

[Ashish_Mathur]

Hi,

For your second question, write this measure

Ct = countrows(Data)

Hope this helps.

[jdbuchanan71]

In PowerQuery, select your Sensor and Date column then go to Transform > Unpivot > Unpivot other columns.

Then just rename the columns after they are unpivoted and fix the data types if needed.