Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
3 years ago
Solved

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

 I have TableA as:   ID                   ORDER      CURRENT 1-3                 1                N 1-4                 2                N 1-5                 3                N 1-6            ...
Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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's avatar
EaglesTony
Post Prodigy
3 years ago

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's avatar
EaglesTony
Post Prodigy
3 years ago

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 ?