Forum Discussion
How do I filter records based off column values in another table(similiar to "IN" for SQL)
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.
Hi
Join with JoinKind.Inner
= Table.NestedJoin(TableA, {"Sprint"}, TableB, {"SprintName"}, "TableB", JoinKind.Inner)Stéphane
= Table.AddColumn(#"Changed Type", "Custom",each(x) => Table.SelectRows(TableB, each [StartDate] <= x[CreateDate] and [EndDate] >= x[CreateDate]){0}[Increment])Stéphane
9 Replies
Hi
Join with JoinKind.Inner
= Table.NestedJoin(TableA, {"Sprint"}, TableB, {"SprintName"}, "TableB", JoinKind.Inner)Stéphane
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)
FYI: There is no relationship between the two tables.
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
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.
Hi
Add a new column on TableA
(x) => Table.SelectRows(TableB, each [StartDate] <= x[CreateDate] and [EndDate] >= x[CreateDate]){0}[Increment]
Stéphane
