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
184Tukrish
184English
1255Arabic
1255English
1255Turkish

 

and I want to show this data like this 

PersonID Language1 Language2 Language3
184TukrishEnglish 
1255ArabicEnglishTurkish

 

If Anyone have any idea please help 

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

     

3 Replies

  • 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"

     

  • dufoq3's avatar
    dufoq3
    Community Champion
    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