Unpivot and Pivot without Errors in Power Query | Avoiding Error: "There weren't enough elements"
#powerquery #power_query
Unpivot and Pivot without Errors in Power Query | Avoiding "Expression.Error: There weren't enough elements in the enumeration to complete the operation."
0:00 Intro
1:07 Prevent Errors
1:59 Recovery Step 1 Group By
2:18 Recovery Step 2 Add Index to _
3:30 Recovery Step 3 Perform Pivot
Situation:
Sometimes there is a need to Unpivot all Columns, perform an operation on the resultant “Value” column, then restore the original table with the Pivot operation.
However, if all Columns are Unpivoted, you cannot Pivot back without getting the following Error:
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Solution:
To Prevent the Pivot Error, add an index column before the Unpivot Step, and use that column for the “Unpivot Other Columns” operation.
If it’s too late to add the index column, perform the following steps with the Attribute/Value Table:
1. Perform “Group By” operation on Attribute Column
(Note: When using the Table.Group() function, the underscore in each _ represents each grouped table.)
2. Use Table.AddIndexColumn() functions to add index to each grouped table, using _ as the initial table and Expand column with Tables
3. Perform Pivot operation on Attribute column, select Value, and Don’t Aggregate
Advanced Editor Code:
let
Source = null,
T1 = #table({"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10"},{{"marquis", "oscar", "jake", "lester", "paul", "brady", "loyd", "trenton", "erich", "ian"}, {"jacques", "jerald", "lenard", "rigoberto", "aaron", "hung", "marcelino", "samuel", "ramon", "bennie"}, {"heriberto", "wilmer", "brant", "mariano", "ezra", "randal", "graham", "joey", "leslie", "greg"}, {"johnny", "kory", "todd", "aron", "clement", "christopher", "dominique", "damian", "manuel", "refugio"}, {"melvin", "clint", "federico", "corey", "martin", "rupert", "neal", "horace", "emilio", "thanh"}, {"beau", "dallas", "jimmy", "nick", "garrett", "russ", "ferdinand", "dick", "eddy", "ronny"}, {"ava", "emily", "nia", "lucas", "amelia", "emma", "mia", "olivia", "isabella", "charlotte"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(T1, {}, "Attribute", "Value"),
#"Capitalized Each Word" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Proper, type text}}),
#"Grouped Rows" = Table.Group(#"Capitalized Each Word", {"Attribute"}, {{"D", each Table.AddIndexColumn(_, "I", 0,1) }}),
#"Expanded D" = Table.ExpandTableColumn(#"Grouped Rows", "D", {"Value", "I"}, {"Value", "I"}),
#"Pivoted Column" = Table.Pivot(#"Expanded D", List.Distinct(#"Expanded D"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please checkout my book, Power Query M Language Basics Kindle Edition
https://www.amazon.com/dp/B0DM73H3RV
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~