Differences

This shows you the differences between two versions of the page.

--- transformations:pivot [2020/01/22 04:38] – dmitry
+++ transformations:pivot [2025/05/05 17:18] (current) – v5.9.5 updates craigt
@@ Line 1: / Line 1: @@
-===== Pivot =====
+{{ transformations:PivotAction.png}}
+====== PIVOT ======
+Category: Transform / Advanced\\
-Creates a matrix table using values in one column as column labels, and values in another column as the matrix column's values. This transformation is opposite to [[transformations:unpivot|Unpivot]].
+\\
+=====Description=====
+Creates a matrix table from a straight table using values in one column as the column labels, and values in a second column as the matrix columns' values. This action is opposite to [[transformations:unpivot|Unpivot]].\\
-**Example: Winter Olympics medals**
+\\
+=====Use cases=====
+This action is used to make aggregated, cross-tabular tables from data lists.
-^  Country  ^  Season  ^  Medals  ^
+This action functions similarly to Excel's //PivotTable// feature.
-| Canada  |  winter 2010  |  26  |
-| United States  |  winter 2010  |  37  |
-| Norway  |  winter 2010  |  23  |
-| Germany  |  winter 2010  |  30  |
-| Canada  |  winter 2014  |  25  |
-| United States  |  winter 2014  |  28  |
-| Norway  |  winter 2014  |  26  |
-| Germany  |  winter 2014  |  19  |
+\\
+=====Action settings=====
+^Setting^Description^
+|Column labels|Select the column containing the values that will become the column labels of the matrix table (across the top).|
+|Data|Select the column containing the values that will become the data in the body of the matrix table (at the\\ intersections of the rows and columns).  Select an aggregation method to apply to the values.  Options:  //Sum//,\\ //Count//, //Min//, //Max//, //Any//, and //Average//.|
+|Aggregate |Check on to aggregate the values in the selected Data column, and choose an aggregation method. \\ Options: //Sum//, //Count//, //Count distinct//, //Min//, //Max//, //Any//, or //Average//.|
+|Preserve formatting|Select this option when a column of date/time values will be used for column headers to retain the date/time format.  |
+|Column order|Choose whether the resultant columns retain their order or are sorted.  Options: //Automatic// or //Sorted//.|
-Pivot on columns [Season] and [Medals]. Aggregation - Any.
+\\
+=====Remarks=====
+Note that grouping works implicitly in the //Pivot// action. Groups are formed by the combination of values in other columns that are not labels or data. Values in a group are aggregated into one row. For instance, in the example below, the "Country" column was used for implicit grouping because the two other columns, "Season" and "Medals", were used for the labels and data, respectively.
+If a pivoted dataset has only two columns, one of which is used for data, and the other for labels, the resulting dataset will always have only one row.
+To drill down to the cross-tabular results using specific dimensions, remove any columns not required in the matrix table prior to inserting the //Pivot// action.\\
+\\
+=====Examples=====
+====Example #1====
+>Summarize the data below in a matrix table showing the number of medals won by Country (rows) and Season (columns).\\
+===Before (source table)===
+^  Country  ^  Season  ^  Medals  ^
+| Canada  |  Winter 2010  |  26  |
+| Norway  |  Winter 2010  |  23  |
+| Germany  |  Winter 2010  |  30  |
+| Canada  |  Winter 2014  |  25  |
+| Norway  |  Winter 2014  |  26  |
+| Germany  |  Winter 2014  |  19  |
-Result:
+===After (result table)===
-^  Country  ^  winter 2010  ^  winter 2014  ^
+^  Country  ^  Winter 2010  ^  Winter 2014  ^
-| Canada  |  26  |  25  |
+| **Canada**  |  **26**  |  **25**  |
-| United States  |  37  |  28  |
+| **Norway**  |  **23**  |  **26**  |
-| Norway  |  23  |  26  |
+| **Germany**  |  **30**  |  **19**  |
-| Germany  |  30  |  19  |
-Note that grouping works implicitly in the "Pivot" action. Groups are formed by combination of values in other columns that not labels or data. Values in a group are aggregated into 1 row. For instance in the example above, column [Country] was used for implicit grouping, because two other columns, [Season] and [Medals], were used for labels and data respectively.
+===Action parameters===
+> Column labels: Season
+> Data: Medals
+> Aggregation method: Any
-If a pivoted dataset has only two columns, one of which is used for data, and the other for labels), then the resulting dataset will always have only 1 row.
+\\
+=====Community examples=====
+  * [[https://community.easymorph.com/t//1351/1|How to order columns by a list of columns]] ([[https://community.easymorph.com/uploads/short-url/bKeuXUgwKo1alDrA03LY5z3tPJv.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Reordered columns//; Action position: //3//)
+  * [[https://community.easymorph.com/t//1716/2|Transpose with grouping]] ([[https://community.easymorph.com/uploads/short-url/voIH8WsKZnRm0MVr8UsSvfEDmC1.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Book1.xlsx//; Action position: //5//)
+  * [[https://community.easymorph.com/t//1926/3|Sort column in an export alphabetically (tab)]] ([[https://community.easymorph.com/uploads/short-url/b1cFUp2TMl0yEassYLlT4oO3hxp.morph|Project]]; Module: //Main//; Group: //Enum/pivot/append//; Table: //Template table to append to//; Action position: //3//)
+  * [[https://community.easymorph.com/t//2220/1|Example: obtaining stock price history from web API]] ([[https://community.easymorph.com/uploads/short-url/q46H2C6P5gxJt02P2TexolJXvBK.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //Stock history//; Action position: //8//)
+  * [[https://community.easymorph.com/t//2321/4|Collapsing multiple rows into one]] ([[https://community.easymorph.com/uploads/short-url/j6CBEKHa76q1v9oQqui4UPWN7ro.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //Method 1//; Action position: //4//)
-**See also**
+\\
+=====See also=====
   * [[transformations:unpivot|Unpivot]]