Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Pivot multiple rows into columns

Hi all, 

 

I am working on a dataset and have been struggling to transform the dataset into the desired format. I need to to pivot the dataset to make it visually more readable. 

 

Below is that the dataset looks like 

Device,    Estimate, Time,        Value
Device1,  False,     00:00:00,    2278.08
Device1,  False,     00:30:00,    2378.08
Device1,  False,     01:00:00,    2478.08
Device2,  False,     00:00:00,    1278.08
Device2,  True,      00:30:00,    1378.08
Device2,  False,     01:00:00,    1478.08

.....

Device100,  False,     23:30:00,   99999

 

The data is coming in every 30 mins. I would like to pivot the data so that 1 row contains all the reading for a device and display the estimated column e.g. as below

 

 

Please note this is sample data and i will have dozons of devices and 24 hour data with 30 mins intervals. Can anyone please suggest how the above can be achived. 

 

Many thanks

4 Replies

  • Have you tried highlighting both the value and time columns and then unpivoting?

    • Anonymous's avatar
      Anonymous
      Not applicable

      Yes, I tried that but it didnt work for me.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Does anyone have any suggestions please?

    • ShNBl84's avatar
      ShNBl84
      Advocate II

      I don't think PBI is designed for that kind of manipulation. The purpose of PBI is to take data in several formats and then display them afterwards, not reorganize the root data.