Forum Discussion
Filter Column Based On Value From Another Column In Power Query
I have a dataset that looks like this:
| Index | Balance | Date | Order Number | Order Line |
| 1 | 6 | 6/1/2022 | 0 | 0 |
| 2 | 3 | 6/16/2022 | 0 | 0 |
| 3 | 2 | 7/6/2022 | 0 | 0 |
| 4 | -1 | 7/18/2022 | 0 | 0 |
| 5 | -5 | 7/20/2022 | 0 | 0 |
| 6 | -6 | 5/25/2022 | 123456 | 1 |
| 7 | 1 | 6/8/2022 | 0 | 0 |
| 8 | 15 | 7/8/2022 | 0 | 0 |
| 9 | 21 | 7/9/2022 | 0 | 0 |
| 10 | 27 | 7/20/2022 | 0 | 0 |
I want to filter the table so it includes all rows after and including the row where [Order Number] = 123456 and [Order Line] = 1. My thought is if there is a way to look up the index value for that row, then filter on the Index column where [Index] is greater than that value.
So in this example, look up [Order Number] = 123456 and [Order Line] = 1 and return [Index] = 6. Then filter on [Index] >= 6. Is there a way to do this in Power Query (not DAX)? Or is there a better way to filter all rows after and including the row that meets the criteria of one of the columns? The data is such that there would never be more than 1 row with the same order number/order line combination.
I found a solution that worked for me.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7LDQAhCATQXjxrFBQ/tRj7b2MdzJ7goBl5hszegUIMHSdT5sJ8Y9Fz4g541YfdKgSTkR1sNyVSpWlZwKLMxTIaJVySWX4mrk0wJP0zNKGcs38C335HF5q/cssqIfLwy50P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Balance = _t, Date = _t, #"Order Number" = _t, #"Order Line" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Balance", Int64.Type}, {"Date", type date}, {"Order Number", Int64.Type}, {"Order Line", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Order Number] = 123456) and ([Order Line] = 1)), Index = #"Filtered Rows"{0}[Index], #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Index] >= Index) in #"Filtered Rows1"First I filtered to the specific row where Order Number = 123456 and Order Line = 1. Then I used #"Filtered Rows"{0}[Index] to select the Index column value for that row. Then I referenced back to the table in step #"Changed Type" before I filtered to the order, and filtered on the Index column to values greater than the value returned by the Index = #"Filtered Rows"{0}[Index] step. There may be other or better ways to accomplish this, but it worked for my scenario.
6 Replies
I dont know if i understanding your example but you want to get only the rows where order line is equals to 1 ?
Mannai I want to return all rows after and including the row where Order Number = 123456 and Order Line = 1. In this example, row 6 is the row where Order Number = 123456 and Order Line = 1, so I need to return rows 6, 7, 8, 9, and 10.
I need to dynamically return the row where Order Number = 123456 and Order Line = 1. So if that is row 5, then I need to return rows 5, 6, 7, 8, 9, 10. If that row is 2, then I need to return rows 2, 3, 4, 5, 6, 7, 8, 9, 10. Etc.
I found a solution that worked for me.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7LDQAhCATQXjxrFBQ/tRj7b2MdzJ7goBl5hszegUIMHSdT5sJ8Y9Fz4g541YfdKgSTkR1sNyVSpWlZwKLMxTIaJVySWX4mrk0wJP0zNKGcs38C335HF5q/cssqIfLwy50P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Balance = _t, Date = _t, #"Order Number" = _t, #"Order Line" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Balance", Int64.Type}, {"Date", type date}, {"Order Number", Int64.Type}, {"Order Line", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Order Number] = 123456) and ([Order Line] = 1)), Index = #"Filtered Rows"{0}[Index], #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Index] >= Index) in #"Filtered Rows1"First I filtered to the specific row where Order Number = 123456 and Order Line = 1. Then I used #"Filtered Rows"{0}[Index] to select the Index column value for that row. Then I referenced back to the table in step #"Changed Type" before I filtered to the order, and filtered on the Index column to values greater than the value returned by the Index = #"Filtered Rows"{0}[Index] step. There may be other or better ways to accomplish this, but it worked for my scenario.
