none
Creating a Flag for Mismatches RRS feed

  • Question

  • Hi all,

    I'm currently working with a fairly large data set of students in certain programs/disciplines, and would be looking to create a calculated column/flag that will identify students who are listed in a program but whose discipline name/ID doesn't match said program. An example would be:

    
    PROGRAM Discipline Course Name  ID Section Term
    BUSI.BULI.BSN BUSI12 BUS-123 123456             BUS-125-LLC99 2017FA
        BUS-789 123456             BUS-100-LLC99 2017FA
      ELEC ELA-123 123456             ELC-100-LLB99 2018WI
        ELA-456 123456             ELC-200-LLB99 2018WI

    In other words, this student's program should be changed from "BUSI.BULI.BSN" to "ELEC", as they are taking no BUS courses at the start of the new 2018WI term; the BUSI.BULI.BSN should be closed and an ELEC program should be opened to reflect the new discipline they transferred to. I'm just having some writers block in creating a calculation that will identify students who are in the same boat as the one above.

    Thank you in advance, any suggestions would be greatly appreciated!


    Darko


    Wednesday, September 6, 2017 5:58 PM

Answers

  • Hi Darko. This might be more complicated than necessary, but hopefully it will give you some ideas for how to proceed.

    let
        Source = ...,
        #"Filled Down" = Table.FillDown(Source,{"PROGRAM", "Discipline"}),
        #"Inserted First Characters" = Table.AddColumn(#"Filled Down", "First Characters", each Text.Start([PROGRAM], 3), type text),
        #"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters",{"First Characters", "PROGRAM", "Discipline", "Course Name", " ID", "Section", "Term"}),
        #"Inserted First Characters1" = Table.AddColumn(#"Reordered Columns", "First Characters.1", each Text.Start([Discipline], 3), type text),
        #"Reordered Columns1" = Table.ReorderColumns(#"Inserted First Characters1",{"First Characters", "First Characters.1", "PROGRAM", "Discipline", "Course Name", " ID", "Section", "Term"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"First Characters", "PROGRAM Code"}, {"First Characters.1", "Discipline Code"}}),
        #"Inserted First Characters2" = Table.AddColumn(#"Renamed Columns", "First Characters", each Text.Start([Term], 4), type text),
        #"Renamed Columns1" = Table.RenameColumns(#"Inserted First Characters2",{{"First Characters", "Year"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Year", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Year] >= 2018),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {" ID"}, {{"Rows", each _, type table}, {"Mismatched Rows", each Table.SelectRows(_, each [PROGRAM Code] <> [Discipline Code]), type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Possible Mismatch", each Table.RowCount([Mismatched Rows]) > 0),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"PROGRAM Code", "Discipline Code", "PROGRAM", "Discipline", "Course Name", "Section", "Term", "Year"}, {"PROGRAM Code", "Discipline Code", "PROGRAM", "Discipline", "Course Name", "Section", "Term", "Year"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Rows",{"Mismatched Rows"})
    in
        #"Removed Columns"

    Let me know if this helps.

    Ehren


    Wednesday, September 6, 2017 11:31 PM
    Owner

All replies

  • I'm not 100% clear on the logic you want to implement. Can you describe it step-by-step?

    Ehren

    Wednesday, September 6, 2017 8:03 PM
    Owner
  • Hi Ehren,

    Thanks for your reply. 

    So the program of the student whose ID is 123456 should be changed from "BUSI.BULI.BSN" to "ELEC", as they are taking no BUS courses at the start of the new 2018WI term and would therefore need to be flagged somehow.  That is, the program for student 123456 is listed as BUSI.BULI.BSN. They should, however, have been moved to a different program because they are taking ELC courses in the new term (2018WI) and no business courses; i.e, the BUSI program should have been closed at the start of the new 2018WI term because it appears they have switched disciplines. I'd like to build a calculation that identifies similar cases.

    I'm essentially looking for a way to identify dirty data.

    Thanks again.


    Darko

    Wednesday, September 6, 2017 8:59 PM
  • Hi Darko. So you only want to flag them if they have no classes from their stated Program in 2018?

    Ehren

    Wednesday, September 6, 2017 9:31 PM
    Owner
  • Hi Ehren, essentially, yes. 

    One way I was thinking of approaching this was trim the left side of the Program column as well as the Course or Section column, and flag any cases that don't match up on those three characters between the two columns within a term. So the other condition would be to write a query that looks at those difference within 2018FA, then 2018WI, etc...

    Wednesday, September 6, 2017 9:39 PM
  • Hi Darko. This might be more complicated than necessary, but hopefully it will give you some ideas for how to proceed.

    let
        Source = ...,
        #"Filled Down" = Table.FillDown(Source,{"PROGRAM", "Discipline"}),
        #"Inserted First Characters" = Table.AddColumn(#"Filled Down", "First Characters", each Text.Start([PROGRAM], 3), type text),
        #"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters",{"First Characters", "PROGRAM", "Discipline", "Course Name", " ID", "Section", "Term"}),
        #"Inserted First Characters1" = Table.AddColumn(#"Reordered Columns", "First Characters.1", each Text.Start([Discipline], 3), type text),
        #"Reordered Columns1" = Table.ReorderColumns(#"Inserted First Characters1",{"First Characters", "First Characters.1", "PROGRAM", "Discipline", "Course Name", " ID", "Section", "Term"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"First Characters", "PROGRAM Code"}, {"First Characters.1", "Discipline Code"}}),
        #"Inserted First Characters2" = Table.AddColumn(#"Renamed Columns", "First Characters", each Text.Start([Term], 4), type text),
        #"Renamed Columns1" = Table.RenameColumns(#"Inserted First Characters2",{{"First Characters", "Year"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Year", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Year] >= 2018),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {" ID"}, {{"Rows", each _, type table}, {"Mismatched Rows", each Table.SelectRows(_, each [PROGRAM Code] <> [Discipline Code]), type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Possible Mismatch", each Table.RowCount([Mismatched Rows]) > 0),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"PROGRAM Code", "Discipline Code", "PROGRAM", "Discipline", "Course Name", "Section", "Term", "Year"}, {"PROGRAM Code", "Discipline Code", "PROGRAM", "Discipline", "Course Name", "Section", "Term", "Year"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Rows",{"Mismatched Rows"})
    in
        #"Removed Columns"

    Let me know if this helps.

    Ehren


    Wednesday, September 6, 2017 11:31 PM
    Owner