Pivot Error in Power Query

[Maha1]

I am facing issue with pivoting the columns in power query. below is the error and my code:

let
    Source = Xml.Tables(Web.Contents("https://sv211140-cuic1XXXXX")),
    row = Source{0}[row],
    #"Expanded column" = Table.ExpandTableColumn(row, "column", {"Element:Text", "Attribute:index", "Attribute:name"}, {"Element:Text", "Attribute:index.1", "Attribute:name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded column",{"Attribute:index.1", "Attribute:index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Attribute:name"]), "Attribute:name", "Element:Text")
in
    #"Pivoted Column"

[v-xinruzhu-msft · Accepted solution]

Hi @Maha1 

The solution  @BA_Pete  provided is excellent, and i want to do some stretching for your solution @BA_Pete  so that can provide more information for the user.

You need to add an index column group by the attribute type.

let
    Source = Xml.Tables(Web.Contents("https://sv211140-cuic1XXXXX")),
    row = Source{0}[row],
    #"Expanded column" = Table.ExpandTableColumn(row, "column", {"Element:Text", "Attribute:index", "Attribute:name"}, {"Element:Text", "Attribute:index.1", "Attribute:name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded column",{"Attribute:index.1", "Attribute:index"})
   #"Grouped Rows" = Table.Group(#"Removed Columns", {"Attribute:name"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Element:Text", "Index"}, {"Element:Text", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[#"Attribute:name"]), "Attribute:name", "Element:Text")
    in
    #"Pivoted Column"

Then expanded it, and then pivot it.

If this help, please consider to mark the solution and the solution @BA_Pete  provided for more people to refer to.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @Maha1 ,

You've not included the actual error description in your post, but my guess is it's a "not enough items to enumerate" error.

If so, then try adding an Index column to the table before you pivot.

Pete