How To: Transpose rows into columns(pivot).

Goal

Understand how to transform a dataset and transpose rows into columns making initial column names a part of the final record.
 

Learn

• Working on the below sample data (Sheet InitialData):

• Create a list and type column names in order of appearance: LIST("<Column1>";"<Column2>";"<Column3>";....)
• Create a list containing the values for the parameter columns: LIST(#InitialData!Parameter1;#InitialData!Parameter2;#InitialData!Parameter3).

• Create a new Sheet (FinalData) and reference the #Country column from the InitialData Sheet.
• Expand the lists of ParameterNames and ParameterValues:
  • Parameter name: LISTELEMENT(#InitialData!ParameterNames;EXPAND(RANGE(0;SIZE(#InitialData!ParameterNames)-1)))
  • Parameter value: LISTELEMENT(#InitialData!ParameterValues;INDEXOF(#InitialData!ParameterNames;#FinalData!ParameterName))