Forum Discussion
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 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
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!
10 Replies
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
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 TransformLet'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!
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
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 ?
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
