How do I filter records based off column values in another table(similiar to "IN" for SQL)

[EaglesTony]

Hi,

  I have tableA with a 4 records and a column named Sprint and need to use these values to filter on another table.

  tableB has a column named SprintName with similiar values, but I want to filter these records based on tableA.

  In SQL it is something like "WHERE tableA.Sprint IN (tableB.SprintName)".

Thank you.

[slorin · Accepted solution]

Hi

Join with JoinKind.Inner

= Table.NestedJoin(TableA, {"Sprint"}, TableB, {"SprintName"}, "TableB", JoinKind.Inner)

Stéphane 

[slorin]

There is never relationship between the two tables in Power Query.

You want a DAX Mesure ?

I dont' understand, 5/1/2023 = increment 2 not 3 

Stéphane

[EaglesTony]

5/1/2023 is on TableA, so I want to lookup on TableB and since it is between 4/21/2023 -  5/1/2023 (and equals 5/1/2023), take the number '2' from TableB and make that a new column on TableA.

[slorin]

Hi

Add a new column on TableA 

(x) => Table.SelectRows(TableB, each [StartDate] <= x[CreateDate] and [EndDate] >= x[CreateDate]){0}[Increment]

 Stéphane

[EaglesTony]

Is this M or DAX ?...

[EaglesTony]

I have the following on TableA

= Table.AddColumn(#"Changed Type", "Custom", each (x) => Table.SelectRows(TableA, each [StartDate] <= x[CreateDate] and [EndDate] >= x[CreateDate]){0}[Increment])

However, it is adding the column without the "Increment" column, but says "Function"

[slorin · Accepted solution]

= Table.AddColumn(#"Changed Type", "Custom", each (x) => Table.SelectRows(TableB, each [StartDate] <= x[CreateDate] and [EndDate] >= x[CreateDate]){0}[Increment])

Stéphane 

[EaglesTony]

hi,

  I have a slighltly different scenerio.

  TableA has multiple rows and each with a CreateDate column on it.

  TableB has multiple rows with date ranges on it with StartDate and EndDate columns on it and a column I need to put on TableA called Increment.

  If TableA has 5/1/2023 and TableB has the following:

Increment   StartDate       EndDate

1                  4/1/2023       4/20/2023

2                  4/21/2023     5/1/2023

3                  5/2/2023       5/20/2023

I want TableA to have the number 3 (from increment on TableA)

[EaglesTony]

FYI: There is no relationship between the two tables.

[slorin · Accepted solution]

Hi

Join with JoinKind.Inner

= Table.NestedJoin(TableA, {"Sprint"}, TableB, {"SprintName"}, "TableB", JoinKind.Inner)

Stéphane