locked
Creating Conditional Columns Filtered Within Rows RRS feed

  • Question

  • Hi all,

    I'm currently working with a fairly large data set (for which I included a sample), and am having some issues developing a flags that will filter within rows.

    For example, I'm currently analyzing test data that requires students take a test at one time, then come back to take the test again at another, later date. So Student 101 would have two entries across a few variables:

    However, there a couple of conditions I'm trying to build in:

    1). The re-test can't have happened two weeks BEFORE the initial test, or four weeks AFTER.

    2). I'm also looking to flag discrepencies within IDs and TestStart and EndTimes (for example, both test start and test end should occur on the same day, as the test duration is only a few hours).

    So ID101 would have a "Invalid Case" flag, as more than four weeks have elapsed between test and retest

    I have a feeling this is something that could be more effectively done in power pivot, but just not quite sure how to get there.

    (The data in the attached workbook is also connected to powerpivot and powerquery)

    Test Retest Workbook

    Currently using Office 365 ProPlus.

    Any help is greatly appreciated, thanks in advance!

    Friday, November 23, 2018 10:25 PM

Answers

  • On examining your original workbook, I think that I figured out what you need. See the following solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = 
            Table.TransformColumnTypes(
                Source,
                {
                 {"ID", type text}, 
                 {"TestStartTime", type datetime}, 
                 {"TestEndTime", type datetime}, 
                 {"Gender", type text}, 
                 {"Time", type text}, 
                 {"Device", type text}
                }
            ),
        GroupedID = Table.Group(ChangedType, {"ID"}, {{"Table", each _, type table}}),
        AddedTimeElapsedFlag = 
            Table.TransformColumns(
                GroupedID, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Time Elapsed Flag", 
                          each if i[TestStartTime]{1} - i[TestStartTime]{0} < #duration(14, 0, 0, 0) or 
                                  i[TestStartTime]{1} - i[TestStartTime]{0} > #duration(28, 0, 0, 0) then
                                  "Invalid" else "Valid"
                      )
                }
            ),
        AddedTestOnDifferentDaysFlag = 
            Table.TransformColumns(
                AddedTimeElapsedFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Test on Different Days Flag", 
                          each if Date.Day(DateTime.Date([TestEndTime])) - Date.Day(DateTime.Date([TestStartTime])) > 0 then
                                  "Invalid" else "Valid"
                      )
                }
            ),
        AddedDeviceFlag = 
            Table.TransformColumns(
                AddedTestOnDifferentDaysFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Device Flag", 
                          each if List.IsDistinct(i[Device]) then
                                  "Invalid" else "Valid"
                      )
                 }
            ),
        AddedGenderFlag =
            Table.TransformColumns(
                AddedDeviceFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Gender Flag", 
                          each if List.IsDistinct(i[Gender]) then
                                  "MisMatch" else "Match"
                      )
                 }
             ),
    
        CombinedTables = Table.Combine(AddedGenderFlag[Table])
    in
        CombinedTables

    Saturday, November 24, 2018 10:29 PM
  • each if i[TestStartTime]{1} - i[TestStartTime]{0} < #duration(0, 0, 0, 0) then



    Monday, November 26, 2018 1:57 AM

All replies

  • The re-test can't have happened two weeks BEFORE the initial test

    1) How can a retest take place any time before the initial test? So the retest can occur one week before the initial test???

    2) Where in the world are there 31 days in November?

    Saturday, November 24, 2018 7:11 PM
  • I actually ended up finding a solution to the date issue, I'm just now stuck on creating a flag for mismatches in gender between the same respondent. 

    For example,

    My data are set up so that each ID has two entries, and ID101 for instance might have a gender of Male on one row, but Female on the next row. 

    Would there be a way to flag those discrepancies?

    I've attached a workbook which will further clarify the issue.

    NewDataSet

    Saturday, November 24, 2018 10:02 PM
  • On examining your original workbook, I think that I figured out what you need. See the following solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = 
            Table.TransformColumnTypes(
                Source,
                {
                 {"ID", type text}, 
                 {"TestStartTime", type datetime}, 
                 {"TestEndTime", type datetime}, 
                 {"Gender", type text}, 
                 {"Time", type text}, 
                 {"Device", type text}
                }
            ),
        GroupedID = Table.Group(ChangedType, {"ID"}, {{"Table", each _, type table}}),
        AddedTimeElapsedFlag = 
            Table.TransformColumns(
                GroupedID, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Time Elapsed Flag", 
                          each if i[TestStartTime]{1} - i[TestStartTime]{0} < #duration(14, 0, 0, 0) or 
                                  i[TestStartTime]{1} - i[TestStartTime]{0} > #duration(28, 0, 0, 0) then
                                  "Invalid" else "Valid"
                      )
                }
            ),
        AddedTestOnDifferentDaysFlag = 
            Table.TransformColumns(
                AddedTimeElapsedFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Test on Different Days Flag", 
                          each if Date.Day(DateTime.Date([TestEndTime])) - Date.Day(DateTime.Date([TestStartTime])) > 0 then
                                  "Invalid" else "Valid"
                      )
                }
            ),
        AddedDeviceFlag = 
            Table.TransformColumns(
                AddedTestOnDifferentDaysFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Device Flag", 
                          each if List.IsDistinct(i[Device]) then
                                  "Invalid" else "Valid"
                      )
                 }
            ),
        AddedGenderFlag =
            Table.TransformColumns(
                AddedDeviceFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Gender Flag", 
                          each if List.IsDistinct(i[Gender]) then
                                  "MisMatch" else "Match"
                      )
                 }
             ),
    
        CombinedTables = Table.Combine(AddedGenderFlag[Table])
    in
        CombinedTables

    Saturday, November 24, 2018 10:29 PM
  • On examining your original workbook, I think that I figured out what you need. See the following solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = 
            Table.TransformColumnTypes(
                Source,
                {
                 {"ID", type text}, 
                 {"TestStartTime", type datetime}, 
                 {"TestEndTime", type datetime}, 
                 {"Gender", type text}, 
                 {"Time", type text}, 
                 {"Device", type text}
                }
            ),
        GroupedID = Table.Group(ChangedType, {"ID"}, {{"Table", each _, type table}}),
        AddedTimeElapsedFlag = 
            Table.TransformColumns(
                GroupedID, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Time Elapsed Flag", 
                          each if i[TestStartTime]{1} - i[TestStartTime]{0} < #duration(14, 0, 0, 0) or 
                                  i[TestStartTime]{1} - i[TestStartTime]{0} > #duration(28, 0, 0, 0) then
                                  "Invalid" else "Valid"
                      )
                }
            ),
        AddedTestOnDifferentDaysFlag = 
            Table.TransformColumns(
                AddedTimeElapsedFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Test on Different Days Flag", 
                          each if Date.Day(DateTime.Date([TestEndTime])) - Date.Day(DateTime.Date([TestStartTime])) > 0 then
                                  "Invalid" else "Valid"
                      )
                }
            ),
        AddedDeviceFlag = 
            Table.TransformColumns(
                AddedTestOnDifferentDaysFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Device Flag", 
                          each if List.IsDistinct(i[Device]) then
                                  "Invalid" else "Valid"
                      )
                 }
            ),
        AddedGenderFlag =
            Table.TransformColumns(
                AddedDeviceFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "Gender Flag", 
                          each if List.IsDistinct(i[Gender]) then
                                  "MisMatch" else "Match"
                      )
                 }
             ),
    
        CombinedTables = Table.Combine(AddedGenderFlag[Table])
    in
        CombinedTables

    Thanks Colin! Yes this is exactly what I was looking for. And yes, to clarify, the logic for the 2 and 4 week time elapsed would be:

    if fewer than 2 weeks has passed at the point of the re-test administration, then invalid,
    if more than 4 weeks has passed at the point of the re-test, then invalid
    else, 

    valid.

    It looks like this is exactly what your solution is doing though.

    Thank you! Works amazingly.

    Saturday, November 24, 2018 11:55 PM
  • Just had one more quick question.

    If I wanted to create a flag that identifies whether re-tests occurred before the initial test (due to mis-entered data).

    Here's the code I have so far, but it doesn't seem to be working correctly

        AddedTestRetestFlag = 
            Table.TransformColumns(
                AddedDOBFlag, 
                {"Table", (i) => 
                      Table.AddColumn(
                          i,
                          "TestRetest", 
                          each if i[TestStartTime]{1} - i[TestStartTime]{0} <> 0 then 
                                  "Valid" else "Invalid"
                      )
                }
            ),

    Sunday, November 25, 2018 12:24 AM
  • each if i[TestStartTime]{1} - i[TestStartTime]{0} < #duration(0, 0, 0, 0) then



    Monday, November 26, 2018 1:57 AM
  • each if i[TestStartTime]{1} - i[TestStartTime]{0} < #duration(0, 0, 0, 0) then



    Thanks Colin. Much appreciated!
    Monday, November 26, 2018 9:04 PM