How to present data from a matrix style csv (date/time vs location)

[gspritchard]

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?

[tex628 · Accepted solution]

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

[gspritchard]

Thank 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.