cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ola
Frequent Visitor

Pivot text to table

 

Is it possible to do a Pivot Text Table, with PowerQuery ? 

 

 

ContentType --->HeaderVotesCommentStatus
Pivot tables and breakdown treesHeader Pivot tables and breakdown trees6,50562 commentsstarted
6,505Votes Custom branding/colour scheme of dashboard (custom css?)3,08566 commentsstarted
62 commentsComments send report or dashboard as email3,536106 commentsstarted
startedStatus Refresh dashboards tiles when i change a report39521 commentsstarted
1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

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

 

Pivot Column Solved.png

View solution in original post

6 REPLIES 6
richbenmintz
Super User I
Super User I

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

 


Raw DataRaw Data

Select Pivot TransformSelect Pivot Transform

select the column that contains the headers to pivot 

-> select Pivot transform from transform ribbon

     -> select advanced and choose no aggregation

 

Transformed DataTransformed Data





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@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!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

Pivot Column Error.png

Sean
Community Champion
Community Champion

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

 

Pivot Column Solved.png

View solution in original post

Ola
Frequent 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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors