Pivot columns with error

[ngct1112]

Hi All,

I am trying to pivot the the "column" but it shows error as "Expression.Error: There were too many elements in the enumeration to complete the operation.",

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4ZhSAwMj82KFsMSc0lSlWB2IlBMQG6HwjME8I9zajKAKDS3BXGMcKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column", type text}, {"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column]), "Column", "Value")
in
    #"Pivoted Column"

Source:

ID	Column	Value
1	A	A's Value
1	B	2
1	B	3
2	A	A's Value
2	B	19
3	A	A's Value

Desired Output:

ID	A	B	B2
1	A's Value	2	3
2	A's Value	19
3	A's Value

Appreciated if any idea!

[Fowmy · Accepted solution]

@ngct1112 

You cannot Pivot as you have a duplicate of B for ID 1. You need to do some additional steps before performing Pivot.
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes given below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyzs8pzc0DMsISc0pTlWJ1opUMgTxHEI4pNTAwMi9WQJVyAmIjFJ4xmGeEW5sRVKGhJZhrjENlLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Column", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID", "Column"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [ID=nullable number, Column=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Column", "Value", "Index"}, {"ID", "Column", "Value", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index] = 1 then [Column] else [Column] & Number.ToText([Index] )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

[ngct1112]

It looks good on my model. Appreciated