Learn about the Community. Join our Core Community to Get Started

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: 
Highlighted

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