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...
Need Help
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
TheNicholas27's avatar
TheNicholas27
New Member
2 years ago

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

 

Sincerely,

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

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

    • TheNicholas27's avatar
      TheNicholas27
      New Member
      2 years ago

      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