Generate new table for Network Navigator visual

You could do this in the query editor by doing a self merge of this table with itself, joined on the Course column, expanding the Student column, and then filtering away the rows where the the same student is twice on the same row.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Call your original table Class or update the code below with the correct name.

let
    Source = Table.NestedJoin(Class, {"Course"}, Class, {"Course"}, "Class", JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(Source, "NumRows", each Table.RowCount([Class]), Int64.Type),
    #"Expanded Class" = Table.ExpandTableColumn(#"Added Custom1", "Class", {"Student_ID"}, {"Student_ID.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Class", "SameStudent", each if ([Student_ID] = [Student_ID.1] and [NumRows]>1) then "Y" else "N"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([SameStudent] = "N")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SameStudent", "NumRows"})
in
    #"Removed Columns"

 

 

You could also do this with a DAX table with the code below.  I called your table "Class".

 

Same Class =
VAR StudentClass =
    SELECTCOLUMNS ( Class, "Student", Class[Student_ID], "Class", Class[Course] )
VAR NewTable =
    GENERATE (
        StudentClass,
        VAR vThisStudent = [Student]
        VAR vThisCourse = [Class]
        RETURN
            CALCULATETABLE (
                VALUES ( Class[Student_ID] ),
                Class[Course] = vThisCourse,
                Class[Student_ID] <> vThisStudent
            )
    )
RETURN
    NewTable

 

 

Pat

 

Hi DucLuong ,

You can try this query to generate the new table:

let
    Source = Table.NestedJoin(Table, {"Course"}, Table, {"Course"}, "Table", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Count", each Table.RowCount([Table])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Count] > 1 then [Table] else null),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Student_ID", "Course"}, {"Custom.Student_ID", "Custom.Course"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Course", "Table", "Count"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Custom.Student_ID] <> [Student_ID]),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Custom.Student_ID", type text}, {"Custom.Course", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom.Student_ID", "Contact"}, {"Custom.Course", "Course"}})
in
    #"Renamed Columns"

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.