Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Transforming rows into columns

Hi Community,

 

I have a table with rows where every name is part of a group.

My objective is to produce a visual with each of the groups as column headers and each name as rows.

I couldnt get this done with pivot/unpivot, but that might be a limitation on my side.

 

Any thoughts would be much appreciated.

 

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous 

     

    Try this.

    Please see attached file for steps

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRci/KLy0AsmJ1oIJG2ASNsQmaYBM0hQsaIQTNsAmawwWNEYIW2AQtkQVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Group = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"ALL", each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Name", "Index"}, {"Name", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded ALL", List.Distinct(#"Expanded ALL"[Group]), "Group", "Name"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"