Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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.
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.
Yes you can with lookupvalue in dax
@Anonymous I need to do this in Power Query, not using DAX
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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
89 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
118 | |
103 | |
76 | |
66 |