Need help to select certain rows from one table based off another

[EaglesTony]

 I have TableA as:

ID                   ORDER      CURRENT

1-3                 1                N

1-4                 2                N

1-5                 3                N

1-6                 4                N

2-1                 5                N

2-2                 6                Y

2-3                 7                N

2-4                 8                N

2-5                 9                N

2-6                 10                N

First I need to select 5 rows BEFORE the one that has the "Y" in current, so I need to grab 1-1 through 2-1 into  a table (NOTE: "Y" might be in 2-4 as the date moves forward, so in this instance I would need 1-5 through 2-3). I was using an ORDER column thinking someway I could subtract using this to get my rows, but not sure how ???

I want the resulting table to have 1-1,1-2,1-3,1-4,1-5,1-6,2-1.

If I can get that working I can ask the 2nd part of the question instead of being long winded here.

Thank you

[EaglesTony]

The 2nd part I now face, is that now that my 2nd table is filtered and I have 5 rows, these rows each contain the following columns:

Start    Order

ABC1      1

ABC2      2

ABC3      3

ABC4      4 

ABC5      5

Now what I want to do is use the Start range from these rows as a filter to a 3rd table.

3rd table has rows:

Item    Start

1          ABC1

1          ABC3

1          ABC7 

2          ABC2

2          ABC3

2          ABC8

So I want to filter this 3rd table to only show those that are in ABC1-ABC5, so the result would be:

Item    Start

1          ABC1

1          ABC3

2          ABC2

2          ABC3

Thanks

[EaglesTony]

I misttyped this:

 I have TableA as:

ID                   ORDER      CURRENT

1-3                 1                N

1-4                 2                N

1-5                 3                N

1-6                 4                N

2-1                 5                N

2-2                 6                Y

2-3                 7                N

2-4                 8                N

2-5                 9                N

2-6                 10                N

First I need to select 5 rows BEFORE the one that has the "Y" in current, so I need to grab 1-3 through 2-1 into  a table (NOTE: "Y" might be in 2-4 as the date moves forward, so in this instance I would need 1-5 through 2-3).

I want the resulting table to have 1-3,1-4,1-5,1-6,2-1.

In SQL I could do something like "Select where Order >=1 and Order <=5", but not sure how to do it in M Query.

If I can get that working I can ask the 2nd part of the question instead of being long winded here.

Thank you

[EaglesTony]

I think I figure it out, it is just the single value:

= Table.SelectRows(#"Sorted Rows", each
List.Contains(
#"newtable"[Start], [Start]))

I think this takes all the value from the first table (1-5) and filters the 2nd table with these values ?

[m_dekorte]

Hi @EaglesTony,

You could give this a go

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ1VtJRMgRiPzCO1QGJmQDZRmhipkC2MZqYGZBtgiJmpAsyyxRNDGQWSG0kkhjILHM0dSCzLKBikVAxkFmWaOpAZhkaIARjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ORDER = _t, CURRENT = _t, CURRENT2 = _t]),
    Transform = 
        [
            t = Table.FirstN( Source, List.PositionOf( Source[CURRENT2], "Y")),
            c = Table.RowCount(t),
            r = if c >5 then Table.Skip( t, (c-5)) else t
        ][r]
in
    Transform

Let's break this logic down, step by step:

I started by adding a manual step called Transform, used a record-expression to generate reusable pieces of code (this can be replaced by a let-expression). 

What do the fields in this record return?

t = Table.FirstN( Source, List.PositionOf( Source[CURRENT2], "Y")),

Returns a table with fewer rows, depending on where the value "Y" is found in the column [CURRENT2]

c = Table.RowCount(t),

Returns the number of rows in t

r = if c >5 then Table.Skip( t, (c-5)) else t

Returns a table with a maximum of 5 rows

After the closing square bracket, I've selected the value for the field r

][r]

Change the list Source[CURRENT2] intoSource[CURRENT]  to test your first scenario.

Ps. If this helps solve your query please mark this post as Solution, thanks!

[EaglesTony]

What I was able to do in the meantime was:

1) Extract just the row with "Y" in it to a new table and create a column "Start" and "End", which are whole numbers (in this case they are 1 and 5 respectively) based on taking the order 6 and subtracting 5 from it to get "1" as my "Start" and subtracting 1 from it to get "5" as my "End".

Now what I need to do is use these values from this new table to extract rows from another table(in this case the rows that have 1,2,3,4,5 as Order). I tried= Table.SelectRows(#"Changed Type1", each List.Contains(#"newtable"[Start"],[ORDER])), but this only gets me the 1 row that matches "Start" (or the number 1).

I need a range of some sort or condition to check >=1 AND <= 5

[m_dekorte]

Hi @EaglesTony,

You will need to apply the list initializer (a set of { } curly brackets) to generate a list with sequential, incremental values, like below:

Table.SelectRows(#"Changed Type1", each 
    List.Contains(
        { #"newtable"[Start]..#"newtable"[End]-1} , [ORDER] )
    )

Ps. If this helps solve your query please mark this post as Solution, thanks!

[EaglesTony]

It is throwing an error "We cannot apply operator - to types List and Number"

[EaglesTony]

If I take out the -1, it throws a similiar error "We cannot apply operator .. to types List and List"

[m_dekorte · Accepted solution]

Oeps, we returned the list not the list item...

Applying item access will resolve that, you have a newtable with 1 row, that means we can hard code the first item, since lists are zero based, add: {0}

Table.SelectRows(#"Changed Type1", each 
    List.Contains(
        { #"newtable"[Start]{0}..#"newtable"[End]{0}-1} , [ORDER] )
    )

 Ps. If this helps solve your query please mark this post as Solution, thanks!

[EaglesTony]

I had to slightly tweark it to not include the -1, as this was losing the last row (#5)..Thanks!