Generate new table for Network Navigator visual

[DucLuong]

Hello!

I have a table like this:

Student_ID	Course
A	English
A	Math
C	English
D	Math
E	Art

I want to generate a new table that shows the relationship between each student that shares the same class in each row. The purpose is to show which student has shared the same class with each other using the Network Navigator visual by Microsoft. My desired output is as below:

Student_ID	Contact	Course
A	C	English
A	D	Math
C	A	English
D	A	Math
E

There's got to be a pretty straightforward way to do this but I don't know the right wording to look for it.

Many thanks for your help.

Duc

[mahoneypat · Accepted solution]

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

[v-yingjl · Accepted solution]

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.

[DucLuong]

Thanks a lot @v-yingjl . I was able to reproduce it by referring to your attached pbix file. The issue is that my table has about 30,000 rows so it takes a very long time to generate the new table, which is about 1 million rows. But I've made some progress with your solution so thank you very much!

[mahoneypat · Accepted solution]

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

[DucLuong]

Thanks a lot @mahoneypat . Your solution also works. However, it takes a really long time to generate the new table so I used Excel to create the table first, then Power BI just read the data. It wold be great if there's a faster way but this will do for now.