Forum Discussion

SClarke501's avatar
SClarke501
Frequent Visitor
4 years ago
Solved

Why can't I Pivot this table?

I need to turn these Types into columns, but I'm getting:         Expression.Error: There weren't enough elements in the enumeration to complete the operation.         Type      Descripti...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You require a minimum of 3 columns for pivoting to happen. See below code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtIBkbpBqSlKsTrIIk45paloQu5Fqal5YLGAnMS8VKAomMYpHp6RWZKKRdwpJzE5GyzulJ9YAhQGUUiqkUWdivLLgWbHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Description = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Type"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0), type table}}),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Description", "Index"}, {"Description", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Temp", List.Distinct(#"Expanded Temp"[Type]), "Type", "Description")
in
    #"Pivoted Column"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

You require a minimum of 3 columns for pivoting to happen. See below code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtIBkbpBqSlKsTrIIk45paloQu5Fqal5YLGAnMS8VKAomMYpHp6RWZKKRdwpJzE5GyzulJ9YAhQGUUiqkUWdivLLgWbHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Description = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Type"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0), type table}}),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Description", "Index"}, {"Description", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Temp", List.Distinct(#"Expanded Temp"[Type]), "Type", "Description")
in
    #"Pivoted Column"