Require assistance for comparing multiple columns

[TheNicholas27]

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,

[dufoq3]

@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

[TheNicholas27]

Yes although im not quite sure how to enter the code.

Sincerely,

[ManuelBolz]

Hello

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

You can replace the highlighted area from my screenshot with your source or last step.

Best regards from Germany
Manuel Bolz

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

[TheNicholas27]

Im trying but it gives me the error on the column name, even if i change the column name.

Im sorry, im no programmer therefore i don't understand where im going wrong.

let
Type = Table.TransformColumnTypes(Source,{{"Student district ID", Int64.Type}, {"Student Names", type text}, {"Student names having taken the test", type text}}),
Students = Type[[StudentNumber],[StudentName]],
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 names having taken the test"})
in
Expand

[v-xinruzhu-msft]

Hi,
Thanks for the solutions @ManuelBolz  and @dufoq3  provided, and i want to offer some more information for user to refer to.

hello @TheNicholas27 , you need to change your code to the following.

let
Source=.....(your souce)
Type = Table.TransformColumnTypes(Source,{{"Student district ID", Int64.Type}, {"Student Names", type text}, {"Student names having taken the test", type text}}),
Merge = Table.NestedJoin(Type, {"Student names having taken the test"}, Type, {"Student Names"}, "Test", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "Test", {"Student district ID"}, {"Student district ID.1"})
in
Expand

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ManuelBolz]

Hello @TheNicholas27 

okay, step by step... 

Somewhere in Power Query you loaded the table that you showed as an example in your second post.

Please share this M-Code with us so that we can add the correct solution.


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

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]

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

[ManuelBolz]

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

[TheNicholas27]

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

Student district ID	Student Names	Student names having taken the test
5555	Archibald, Alexander	Divers, Anthony
5341	Bard, Christopher	Fold, Marc
7521	Chambers, Leon	Archibald, Alexander
6123	Divers, Anthony
8196	Extravagant, Pete
9865	Fold, Marc

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

[ManuelBolz]

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

[dufoq3]

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).