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

     

slorin's avatar
slorin
Super User
2 years ago

Hi, one more solution

 

let
Source = Your_Source,
GroupedRows = Table.Group(Source, {"PersonID"}, 
      {{"All", each #table(
            {"PersonID"} & List.Transform({ 1..Table.RowCount(_)}, each "Language" & Text.From(_)),
            {{[PersonID]{0}} & [Language]} )
      }}),
Combine = Table.Combine(GroupedRows[All])
in
Combine

 Stéphane