Forum Discussion

cu-admin's avatar
cu-admin
Regular Visitor
2 years ago
Solved

How to transpose a table visual

Hello Forks,   I have data like this on my power query  PersonID  Language 184 Tukrish 184 English 1255 Arabic 1255 English 1255 Turkish   and I want to show this data...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi cu-admin, different approach here.

     

    Result

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQwUdJRCinNLsoszlCK1YGJuOal58BFjExNgUKORYlJmcnIIlgUhZQWZYOFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PersonID = _t, Language = _t]),
    GroupedRows = Table.Group(Source, {"PersonID"}, {{"Count", Table.RowCount}, {"All", each
        [ a = Table.Transpose(Table.SelectColumns(_, {"Language"})),
          b = List.Transform({ 1..Table.ColumnCount(a) }, (x)=> "Language" & Text.From(x)),
          c = List.Zip({ Table.ColumnNames(a), b }),
          d = Table.RenameColumns(a, c)
        ][d], type table}}),
    ExpandedAll = Table.RemoveColumns(Table.ExpandTableColumn(GroupedRows, "All", List.Transform({ 1..List.Max(GroupedRows[Count]) }, each "Language" & Text.From(_))), {"Count"})
in
    ExpandedAll

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

Use this code. Replace your source appropriately

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"PersonID"}, {{"All", each Record.Combine({[PersonID = Table.FirstValue(_)]} & List.Transform({1..Table.RowCount(_)}, (i)=> Record.AddField([], "Language" & Text.From(i), _[Language]{i-1})))}})[[All]],
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", Record.FieldNames(Record.Combine(#"Grouped Rows"[All])))
in
    #"Expanded All"