Conditional Column Between two tables?

[chq]

I have two sets a of data that look like the following...

ID#     Date Valid                                                                          ID#                         Scan Date

1         01/01/2018                                                                       1                              01/01/2018

2         01/05/2017                                                                       1                              01/02/2018

3         01/05/2018                                                                       1                              01/02/2018

4         01/05/2017                                                                       1                              01/08/2018

                                                                                                     2                              01/04/2017

                                                                                                     2                              01/05/2017

                                                                                                     2                              01/06/2017

                                                                                                     3                              01/10/2018

                                                                                                     4                              01/01/2017



I would like to somehow either combine them or use a formula that can create a new column where it flags any Scan Date that happens before the Valid Date. 

Does anyone know how i would go about doing this?

I also need to be able to count the total number of ID#s so I don't think a merge will work because that will increase the total number of rows. Ideally the finished product would look like this.

ID#                           Scan Date            Date Valid              Early Scan

1                              01/01/2018          01/01/2018            0

1                              01/02/2018          01/01/2018            0

1                              01/02/2018          01/01/2018            0

1                              01/08/2018          01/01/2018            0

2                              01/04/2017          01/05/2017            1

2                              01/05/2017          01/05/2017            0

2                              01/06/2017          01/05/2017            0

3                              01/10/2018          01/05/2018            0

4                              01/01/2017          01/05/2017            1

[parry2k]

@chq assuming you have relation between both these table, you can simply add new column in you "scan detail" table, please change the name of tables and columns in following expression as per your model. 

Early Scan = IF( ScanTable[SCan Date] < RELATED( ValidTable[Valid Date] ), 0, 1 )

[chq]

They share a relationship based on the ID# but when I punch in the formula I am getting an error that there is not a relationship to any table available in the current context? Do I need to link them via another column other than ID#?

[Anonymous · Accepted solution]

Hi chq,

Here's what I did based off your sample date (thanks for providing some!):

Two steps,

1) In your "Scan" table, open the Power Query Editor and click to add a new column, and use this code:

let currentID = [ID]
in 
  List.Single(Table.SelectRows(
    #"Valid",
    each [ID] = currentID
  )[Date Valid])

Here's what it does:
- Sets a variable to hold the value of the current row ID
- Selects the row from the "Valid" table with a matching ID
- Gets the "Date Valid" field from that row (as a list...)
- Retrieves that single item from the list so you have it as a value/date

2) Again, add a new column, and this time use this code:

if [Scan Date] < [Date Valid] then 1 else 0

Alternatively...

perry2k's solution got me thinking - why use 2 columns when you can use 1?  If for some reason you don't need or want the "Date Valid" column, and really just want to see if the scan date was early or not, you can use add a single column and use this combined code:

if (
let currentID = [ID]
in 
  List.Single(Table.SelectRows(
    #"Valid",
    each [ID] = currentID
  )[Date Valid])) <= [Scan Date] then "0" else "1"

It's ugly, but all it's doing is wrapping the code from above if an IF block, and directly checking the lookup result against the scan date.

[parry2k]

@chq it should work if you have one to many relationship between these two tables. can you share pbix if it doesn't contain sensitive data?