Forum Discussion
Ola
9 years agoFrequent Visitor
Pivot text to table
Is it possible to do a Pivot Text Table, with PowerQuery ? Content Type ---> Header Votes Comment Status Pivot tables and breakdown trees Header Pivot tables and breakdow...
Sean
9 years agoCommunity Champion
Sean
9 years agoCommunity Champion
Ola It seems you need an additional column like in the picture (I've named it Idea Number) and then it works :smileyhappy:
- Ola9 years agoFrequent Visitor
Thanks Sean, perfect!
This is how I used it:
let
Source = Web.Page(Web.Contents("https://ideas.powerbi.com/forums/265200-power-bi-ideas/status/1328500?page="&PageNum)),
Data0 = Source{0}[Data],
Children = Data0{0}[Children],
Children1 = Children{2}[Children],
Children2 = Children1{3}[Children],
Children3 = Children2{5}[Children],
Children4 = Children3{1}[Children],
Children5 = Children4{1}[Children],
Children6 = Children5{2}[Children],
Children7 = Children6{10}[Children],
Children8 = Children7{3}[Children],
#"Expanded Children" = Table.ExpandTableColumn(Children8, "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children.1", "Text.1"}),
#"Expanded Children.1" = Table.ExpandTableColumn(#"Expanded Children", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.2", "Name.2", "Children", "Text.2"}),
#"Expanded Children1" = Table.ExpandTableColumn(#"Expanded Children.1", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.3", "Name.3", "Children.1", "Text.3"}),
#"Expanded Children.2" = Table.ExpandTableColumn(#"Expanded Children1", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.4", "Name.4", "Children", "Text.4"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Children.2",{"Kind", "Name", "Kind.1", "Name.1", "Kind.2", "Text.2", "Text.1", "Text"}),
#"Expanded Children2" = Table.ExpandTableColumn(#"Removed Columns", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind", "Name", "Children.1", "Text"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Children2", each ([Name.2] <> null and [Name.2] <> "SPAN") and ([Name.3] <> "DIV" and [Name.3] <> "FORM" and [Name.3] <> "SPAN")),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Test", each if [Kind.3] = "Element" then "Keep" else if Text.Contains([Text.3], "comment") then "Keep" else null ),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Type", each if [Name.2] = "H2" then "Header" else if [Name.3] = "STRONG" then "Votes" else if [Name.3] = "EM" then "Status" else if [Name.4] = "EM" then "Status" else if Text.Contains([Text.3], "comment") then "Comments" else null ),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column1",{"Name.2", "Kind.3", "Name.3", "Kind.4", "Name.4", "Kind", "Name", "Children.1"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([Test] = "Keep")),
#"Added Conditional Column2" = Table.AddColumn(#"Filtered Rows1", "Join", each if [Text] <> null then [Text] else if [Text.4] <> null then [Text.4] else if [Text.3] <> null then [Text.3] else null ),
#"Removed Columns3" = Table.RemoveColumns(#"Added Conditional Column2",{"Text", "Text.4", "Text.3", "Test"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns3", "Index", 0, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Join", "Content"}}),
#"Inserted Integer-Division" = Table.AddColumn(#"Renamed Columns", "PostNo", each Number.IntegerDivide([Index], 4), Int64.Type),
#"Inserted Addition" = Table.AddColumn(#"Inserted Integer-Division", "PostNum", each List.Sum({[PostNo], 1}), type number),
#"Removed Columns2" = Table.RemoveColumns(#"Inserted Addition",{"Index", "PostNo"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Content", "Type", "PostNum"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Type]), "Type", "Content"),
#"Removed Columns4" = Table.RemoveColumns(#"Pivoted Column",{"PostNum"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns4",",","",Replacer.ReplaceText,{"Votes"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value","Comments",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Comments"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Comments", Int64.Type}, {"Votes", Int64.Type}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper}})
in
#"Capitalized Each Word"I