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
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)|
|Energy (kWh)||Energy (kWh)||Energy (kWh)|
2.- We go to the data tab in Excel and select "from range/table"
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.
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)
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
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.