Forum Discussion

TheNicholas27's avatar
TheNicholas27
New Member
2 years ago

Require assistance for comparing multiple columns

Greetings,

 

I have been trying to find any kind of help regarding a task im trying to accomplish.

 

The task goes like this.

 

I have 3 columns,

1 with numbers that are attributed to students.

the 2nd column with the student names.

And the 3rd is the students name that have taken the test.

 

 

I want to create a column that would automatically assign the numbers that are attributed to the student to the column of students name that have taken the test.

 

Is there a way, to get direct matches from the 3rd to the 2nd and then get the number from column 1.

 

I hope this is clear enough.

 

Sincerely,

Need Help

11 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi, yes it is possible, but provide sample data as text (read note below if you don't know how) and expected result based on sample data (can be a screenshot).

  • TheNicholas27's avatar
    TheNicholas27
    New Member
    2 years ago

    Alright here is a fictional list that ressembles closesly my problem.

     

    Student district IDStudent NamesStudent names having taken the test
    5555Archibald, AlexanderDivers, Anthony
    5341Bard, ChristopherFold, Marc
    7521Chambers, LeonArchibald, Alexander
    6123Divers, Anthony 
    8196Extravagant, Pete 
    9865Fold, Marc 

     

    My goal is to join the student district ID to the column of students that have taken the test.

    • ManuelBolz's avatar
      ManuelBolz
      Responsive Resident
      2 years ago

      Hello

      If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

      Here is the customized code based on your sample data.

       

      let
    Quelle = Table.FromRecords({
        [Student district ID = 5555, Student Names = "Archibald, Alexander", Student names having taken the test = "Divers, Anthony"],
        [Student district ID = 5341, Student Names = "Bard, Christopher", Student names having taken the test = "Fold, Marc"],
        [Student district ID = 7521, Student Names = "Chambers, Leon", Student names having taken the test = "Archibald, Alexander"],
        [Student district ID = 6123, Student Names = "Divers, Anthony", Student names having taken the test = ""],
        [Student district ID = 8196, Student Names = "Extravagant, Pete", Student names having taken the test = ""],
        [Student district ID = 9865, Student Names = "Fold, Marc", Student names having taken the test = ""]
    }),
    Type = Table.TransformColumnTypes(Quelle,{{"Student district ID", Int64.Type}, {"Student Names", type text}, {"Student names having taken the test", type text}}),
    Students = Type[[Student district ID],[Student Names]],
    Merge = Table.NestedJoin(Type, {"Student names having taken the test"}, Students, {"Student Names"}, "Test", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Test", {"Student district ID"}, {"Student district ID Taken"})
in
    Expand

       

       

       


      Best regards from Germany
      Manuel Bolz

      🟦Follow me on LinkedIn
      🟨How to Get Your Question Answered Quickly
      🟩Fabric Community Conference
      🟪My Solutions on Github

  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    Hello

    If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

    i hope the following code helps you.

     

    let
    Quelle = Table.FromRecords({
        [StudentNumber = 101, StudentName = "Alice", TestTakenBy = "Eva"],
        [StudentNumber = 102, StudentName = "Bob", TestTakenBy = "Charlie"],
        [StudentNumber = 103, StudentName = "Charlie", TestTakenBy = "Bob"],
        [StudentNumber = 104, StudentName = "David", TestTakenBy = "Alice"],
        [StudentNumber = 105, StudentName = "Eva", TestTakenBy = "Charlie"]
    }),
    Type = Table.TransformColumnTypes(Quelle,{{"StudentNumber", Int64.Type}, {"StudentName", type text}, {"TestTakenBy", type text}}),
    Students = Type[[StudentNumber],[StudentName]],
    Merge = Table.NestedJoin(Type, {"TestTakenBy"}, Students, {"StudentName"}, "Test", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Test", {"StudentNumber"}, {"StudentNumberTakenBy"})
in
    Expand

     


    Best regards from Germany
    Manuel Bolz

    🟦Follow me on LinkedIn
    🟨How to Get Your Question Answered Quickly
    🟩Fabric Community Conference
    🟪My Solutions on Github

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    TheNicholas27, like this?

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY4xD4IwEIX/StO5S8FWGBF10sSdMBzQ2CbYmqMh+O+9djAx0dvu3vfeu67jioYL3uBo3QDzJFgzmw38ZJDOR7caXOjmow3+xXtBjnInSToAEtxadEsMT5vxc0gBV8Axk3tVJLK18BhyzMUE/68sGbQsyh+tgrMsV7LWtJy2iLDCHXwU7Gai+QB1pdX3G1np3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student district ID" = _t, #"Student Names" = _t, #"Student names having taken the test" = _t]),
    MergedQueries = Table.NestedJoin(Source, {"Student names having taken the test"}, Source, {"Student Names"}, "Source", JoinKind.LeftOuter),
    ExpandedSource = Table.ExpandTableColumn(MergedQueries, "Source", {"Student district ID"}, {"SStudent districct IDs having taken the test"})
in
    ExpandedSource
      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Have you read note below my post? BTW. It is power query solution.