Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Solution Sage
Solution Sage

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



I hope this helps,
Richard

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

Proud to be a Super User!


@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



I hope this helps,
Richard

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

Proud to be a Super User!


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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.