New in the Community? Get started here

Schneider Electric Exchange Community

Discuss and solve problems in energy management and automation. Join conversations and share insights on products and solutions. Co-innovate and collaborate with a global network of peers.

Register Now
Resource Advisor - Performance Analytics Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Making csv exports pivot-table friendly

Hi,

 

at first glance, PAM csv exports are not Pivot Table friendly. Here are a few steps to turn your csv export into a table that can be easily analyzed using Excel.

 

This tutorial uses a trend analysis export

 

1.- Open your .csv export with Excel. I usually only work with one measurement (see Electricity Energy), but if needed, "merge" the first and second rows --> If we have a first row with Site name and another with the measurement, we end up with a single row with Site + Measurement. We can use the concatenate function and others. 

 

In this example, the first row, which we will keep, we have done it by concatenating the 2nd and 3rd rows (=CONCAT(A2," ",A3)). we paste as values and delete the 2nd and 3rd rows.

 

 

Date/Time Sant Boi Energy (kWh)Telde Energy (kWh)Vallecas Energy (kWh)
Date/TimeSant BoiTeldeVallecas
 Energy (kWh)Energy (kWh)Energy (kWh)

 

2.- We go to the data tab in Excel and select "from range/table"

bleh.png

 

3.- On the right, we delete the "change type" added step - on my excel version and settings, this step was returning an error. Here, excel was actually converting the date value, but we don't need to do this now.

boh.png

 

4.- We select all of the columns except date time, right click and select "Unpivot Only selected columns". Instead of multiple columns, we'll end up with only three: Date/time, attribure (we can rename to site) and Value (Consumption value)

bah.png

 

5.- Click on close & load (top left), reformat Column A to date/time values, and we have a nice table that can be easily analyzed using a Pivot Table.

 

Hope it helps

 

1 REPLY 1
Highlighted
Lt. Commander

Re: Making csv exports pivot-table friendly

Great summary Adria! 

 

The Data Features of Excel are really impressive and probably way under utilized for ad-hoc type analysis.  This certainly simplifies things a bit for the PAM export rather than copy/paste a bunch of columns.