Forum Discussion
How to present data from a matrix style csv (date/time vs location)
Hi,
I am very new to Power BI but have hoped to use it to present some utilisation data from a building. The data is exported in a csv format with location in the first column, then the rest of the columns spanning date/time in 1hr increments. The data points are they averages of space utilisation in a decimal (a % of 100).
I have been doing a bit of trial and error and found that it works best when transposed - locations as row headings. As when you are looking at a month of data, 24-hours a day, you exceed 700 columns.
However I have hit a couple of points:
1. I cannot filter locations as each is their own data column
2. You can only seem to slice by time on a dropdown list
3. I wish to add a "league table" of max / min utilisated rooms.
4. I hope to use with OKViz Synoptic Panel at the end to show room utilisation, I think the data needs presenting differently to achieve this.
Any advise? Is it something that needs data manipulation before analysis?
Hi,
Try this:
Highlight all the location columns in power query:
Then press unpivot columns:
Should give you the following result:
As for the DateTime, you will need to create a datetime column by combining your date and time columns.
Format [Date] to Date type, and [Time] to Time type:
To:
Then change the data format of your new column to datetime:
That should be all i think,
Br,
J
2 Replies
Hi,
Try this:
Highlight all the location columns in power query:
Then press unpivot columns:
Should give you the following result:
As for the DateTime, you will need to create a datetime column by combining your date and time columns.
Format [Date] to Date type, and [Time] to Time type:
To:
Then change the data format of your new column to datetime:
That should be all i think,
Br,
JThank you - appeared to do the job nicely. I have a few more queries but for now this works to get me started on formatting etc.
