Pivot text to table

[Ola]

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 breakdown trees	6,505	62 comments	started
6,505	Votes		Custom branding/colour scheme of dashboard (custom css?)	3,085	66 comments	started
62 comments	Comments		send report or dashboard as email	3,536	106 comments	started
started	Status		Refresh dashboards tiles when i change a report	395	21 comments	started

[richbenmintz]

There is a Pivot transformation in Power Query, see the steps below

Raw Data

Select Pivot Transform

select the column that contains the headers to pivot 

-> select Pivot transform from transform ribbon

     -> select advanced and choose no aggregation

Transformed Data

[Sean]

@richbenmintz I think @Ola should have posted this a sample data set based on the desired output

Content	Type
Pivot tables and breakdown trees	Header
6,505	Votes
62 comments	Comments
started	Status
Custom branding/colour scheme of dashboard (custom css?)	Header
3,085	Votes
66 comments	Comments
started	Status
send report or dashboard as email	Header
3,536	Votes
106 comments	Comments
started	Status
Refresh dashboards tiles when i change a report	Header
395	Votes
21 comments	Comments
started	Status

That's when you get the error!

[richbenmintz]

@Sean, not sure what error you are referring to? I was simply trying to answer the question as asked

[Sean]

[Sean · Accepted solution]

@Ola It seems you need an additional column like in the picture (I've named it Idea Number) and then it works

[Ola]

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