Forum Discussion
Reducing table size by filtering
Hi,
I'm having a table, let's call it Table A. How can I reduce the table to only rows where ID-Number is 1, so it has only to rows (the first and the last one) left? Important: the table is already filled with extra columns I added after the import of the data.
| ID-Number | Product A | Product B | Product C | Product D |
| 1 | 1,5 | 2 | 7 | 9 |
| 0 | 5 | 8 | 3 | 1 |
-1 | 2 | 3 | 1 | 8 |
1 | 7 | 5 | 6 | 2 |
Any ideas which way I could get the desired result?
Thanks in advance,
Felix
Anonymous
mahoneypat has already provided a solution and full explanation. Here's another one in case it is easier to follow (although I am not sure it will on the first custom column). You'll have however to build some basic PQ knowledge on your own .It first creates a custom column with a list of the ID-Number values for the Contract number in that row. Then it creates another custom column that checks if those values contain 1 and -1. Finally it filters out the Contracts with 1 and -1:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzBDQAhCAR74Y3JoadgLYT+27hFuccQyO7gTtIHMUnCE7MDBZuCndQ29gdkZuC2M7tmk5L+N3bCd666teR1ehEf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract-number" = _t, #"ID-Number" = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract-number", Int64.Type}, {"ID-Number", Int64.Type}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}, {"Product D", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "IDNumbers in contract", each Table.SelectRows(#"Changed Type", (inner)=>(inner)[#"Contract-number"] = [#"Contract-number"])[#"ID-Number"]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Has 1 and -1", each List.Contains([IDNumbers in contract],1) and List.Contains([IDNumbers in contract],-1)), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([#"Has 1 and -1"] = false)) in #"Filtered Rows"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
9 Replies
- amitchandak
Super User
Anonymous , source and target is not clear
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
amitchandak: Maybe the tables below make it clearer for you? I'm in the "data-view" of PowerBI and I want to delete the two rows in the middle by reffering to the ID-Number 0 and -1. Which options do I have to receive the modified table?
Original tabkle
ID-Number Product A Product B Product C Product D
1 1,5 2 7 9 0 5 8 3 1 -1
2 3 1 8 1
7 5 6 2 Modified table:
ID-Number Product A Product B Product C Product D
1 1,5 2 7 9 1 7 5 6 2 - mahoneypat
Microsoft Employee
In the query editor, just add a step to filter your ID Number column to values >= 1.
Regards,
Pat
- AlB
Community Champion
Anonymous
mahoneypat has already provided a solution and full explanation. Here's another one in case it is easier to follow (although I am not sure it will on the first custom column). You'll have however to build some basic PQ knowledge on your own .It first creates a custom column with a list of the ID-Number values for the Contract number in that row. Then it creates another custom column that checks if those values contain 1 and -1. Finally it filters out the Contracts with 1 and -1:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzBDQAhCAR74Y3JoadgLYT+27hFuccQyO7gTtIHMUnCE7MDBZuCndQ29gdkZuC2M7tmk5L+N3bCd666teR1ehEf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract-number" = _t, #"ID-Number" = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract-number", Int64.Type}, {"ID-Number", Int64.Type}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}, {"Product D", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "IDNumbers in contract", each Table.SelectRows(#"Changed Type", (inner)=>(inner)[#"Contract-number"] = [#"Contract-number"])[#"ID-Number"]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Has 1 and -1", each List.Contains([IDNumbers in contract],1) and List.Contains([IDNumbers in contract],-1)), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([#"Has 1 and -1"] = false)) in #"Filtered Rows"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
