Forum Discussion

RMF40's avatar
RMF40
Copper Contributor
Oct 01, 2024

How do I change a table with 2 columns where I have repeating values in both columns into a matrix?

Hopefully I will be able to explain my problem correctly.   I have a table with two columns in it. Column A has students in it, column B has the subject that each student is taking. Both column A a...
Excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 03, 2024

RMF40 

A Power Query variant attached:

let
    Source = TableSource,
    GroupedName = Table.Group(Source, {"Name"},
        {
            {"DATA", each
                Table.Transpose(
                    Table.SelectColumns(_, {"Subject"})
                ),
                type table
            },
            {"Count", Table.RowCount, Int64.Type}
        }
    ),
    RemovedCount = Table.RemoveColumns(GroupedName, {"Count"}),
    SortedName = Table.Sort(RemovedCount, {{"Name", Order.Ascending}}),
    ListMaxCount = { 1..List.Max( GroupedName[Count] ) },
    ExpandedSubject = Table.ExpandTableColumn(SortedName, "DATA",
        List.Transform( ListMaxCount, each "Column" & Text.From(_) ),
        List.Transform( ListMaxCount, each "Subject " & Character.FromNumber(_ +64) )
    )
in
    ExpandedSubject

 

 

Resources