Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to evaluate test results from multiple students?

I have a process that works with a small data set.  But it doesn't scale .... so there must be a better way.  I hope the experts in this group can help.  The data below is a representative subset to ...
Need Help
Show and Tell
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    4 years ago

     

    Aah, gotcha. Sorry.

     

    Try this instead:

    // Call this 'stephAnswers'
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjRU0lEyVIrVATKNgMwkCNMYyCwpKk2F8ExAEjmJydkQrimQa2mpFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [question = _t, answer = _t])
in
    Source

     

    // Call this 'stephTest'
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjZU0lEqBBGGSrE6ML4RkEhC4hsDiZKi0lQkIROQkpzE5GwkMVMgYWkJETBCM9cIzVwjmLlpiTnFqUhiIIPLMzJLkMVABhsaQE0yRjPZGM1kY1QXxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [student = _t, question = _t, answer = _t]),
    addStudentQ = Table.AddColumn(Source, "studentQ", each Text.Combine({[student], [question]}, "-"), type text)
in
    addStudentQ

     

    // Call this 'stephRootTable'
let
    Source = Table.Distinct(Table.SelectColumns(stephTest, "student")),
    addPossQuestion = Table.AddColumn(Source, "possQuestion", each stephAnswers[question]),
    expandPossQuestion = Table.ExpandListColumn(addPossQuestion, "possQuestion"),
    addStudentQ = Table.AddColumn(expandPossQuestion, "studentQ", each Text.Combine({[student], [possQuestion]}, "-"), type text),
    chgTypes = Table.TransformColumnTypes(addStudentQ,{{"possQuestion", type text}})
in
    chgTypes

     

    Apply all three to your model and relate as follows:

     

    stephAnswers[question] > stephRootTable[possQuestion]

    stephTest[studentQ] > stephRootTable[studentQ]

     

    Add a new custom column in stephRootTable as follows:

    ..score = IF(RELATED(stephTest[answer]) = RELATED(stephAnswers[answer]), 1, 0)

     

    Chuck it all in a table as follows:

     

    This should be super-scalable as you're combining lower-resource functions of Power Query with the power of relationships to avoid costly PQ joins.

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
4 years ago

 

Aah, gotcha. Sorry.

 

Try this instead:

// Call this 'stephAnswers'
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjRU0lEyVIrVATKNgMwkCNMYyCwpKk2F8ExAEjmJydkQrimQa2mpFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [question = _t, answer = _t])
in
    Source

 

// Call this 'stephTest'
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjZU0lEqBBGGSrE6ML4RkEhC4hsDiZKi0lQkIROQkpzE5GwkMVMgYWkJETBCM9cIzVwjmLlpiTnFqUhiIIPLMzJLkMVABhsaQE0yRjPZGM1kY1QXxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [student = _t, question = _t, answer = _t]),
    addStudentQ = Table.AddColumn(Source, "studentQ", each Text.Combine({[student], [question]}, "-"), type text)
in
    addStudentQ

 

// Call this 'stephRootTable'
let
    Source = Table.Distinct(Table.SelectColumns(stephTest, "student")),
    addPossQuestion = Table.AddColumn(Source, "possQuestion", each stephAnswers[question]),
    expandPossQuestion = Table.ExpandListColumn(addPossQuestion, "possQuestion"),
    addStudentQ = Table.AddColumn(expandPossQuestion, "studentQ", each Text.Combine({[student], [possQuestion]}, "-"), type text),
    chgTypes = Table.TransformColumnTypes(addStudentQ,{{"possQuestion", type text}})
in
    chgTypes

 

Apply all three to your model and relate as follows:

 

stephAnswers[question] > stephRootTable[possQuestion]

stephTest[studentQ] > stephRootTable[studentQ]

 

Add a new custom column in stephRootTable as follows:

..score = IF(RELATED(stephTest[answer]) = RELATED(stephAnswers[answer]), 1, 0)

 

Chuck it all in a table as follows:

 

This should be super-scalable as you're combining lower-resource functions of Power Query with the power of relationships to avoid costly PQ joins.

 

Pete

Anonymous's avatar
Anonymous
Not applicable
4 years ago

BA_Pete - quick question... what is the long multicharater text after after FromText?  Where does this come from?  Why the reference to a Json document? What is being decompressed?  all the data is coming from spreadsheets... Thx.

FromText("i45WKjZU0lEqBBGGSrE6ML4RkEhC4hsDiZKi0lQkIROQkpzE5GwkMVMgYWkJETBCM9cIzVwjmLlpiTnFqUhiIIPLMzJLkMVABhsaQE0yRjPZGM1kY1QXxwIA",

 

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Steph,

     

    This is just how Power Query represents data that has been created using the 'Enter Data' function.

    It stores it as a text representation of a JSON binary so that it's very quick and easy to share built tables just by copying and pasting the M code. In scenarios such as this, where I want to share a complete, working, solution with someone on a forum, it allows me to send a complete table in text form, and allows you to just paste it wholesale into a blank query using Advanced Editor and have a complete table ready to use.

    Once you've pasted all the tables into PQ at your end, named them as suggested, and tested that my proposed solution works as required, you would then swap out the whole Source step from these queries and replace it with the Source step(s) that point to your actual data.

     

    For example, if I paste the whole of the first code segment into Advanced Editor, like this:

     

    When I hit 'Done', I get this, a fully-formed and formatted table of example data:

     

    Pete