Forum Discussion
Pivot Error in Power Query
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"
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.
2 Replies
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.
