Forum Discussion
How to remove specific rows based on a condition?
Hello everyone,
I have a table in a power bi report organized like you can see below:
And this is what I'm trying to do using power query:
Explaining: I have a column named "REV", and for each item in REV (I may have: REV0, REV1, REV2, REV3 and REV4) I need to get the lastest date for REV, removing all other lines. Note that for some REV (in this case REV1) I have a unique date and should keep it. I tried some approaches, but it didn't work.
If you have any ideas I would appreciate. For more details just ask me.
Data in a table format:
| MODEL | AVERAGE | REV | MONTH | DATE | ITEM_N |
| PMED BASE | 65,93 | REV1 | JUL | 07/04/2022 | 1 |
| ECMW | 65,85 | REV1 | JUL | 07/04/2022 | 2 |
| GEFS | 64,22 | REV1 | JUL | 07/04/2022 | 3 |
| ETA | 65,22 | REV1 | JUL | 07/04/2022 | 4 |
| PMED BASE | 64,49 | REV2 | JUL | 07/05/2022 | 1 |
| ECMW | 64,84 | REV2 | JUL | 07/05/2022 | 2 |
| GEFS | 65,78 | REV2 | JUL | 07/05/2022 | 3 |
| ETA | 64,87 | REV2 | JUL | 07/05/2022 | 4 |
| PMED BASE | 61,04 | REV2 | JUL | 07/06/2022 | 1 |
| ECMW | 62,40 | REV2 | JUL | 07/06/2022 | 2 |
| GEFS | 65,76 | REV2 | JUL | 07/06/2022 | 3 |
| ETA | 64,71 | REV2 | JUL | 07/06/2022 | 4 |
| PMED BASE | 55,70 | REV0 | JUL | 07/01/2022 | 1 |
| ECMW | 55,70 | REV0 | JUL | 07/01/2022 | 2 |
| GEFS | 55,70 | REV0 | JUL | 07/01/2022 | 3 |
| ETA | 55,70 | REV0 | JUL | 07/01/2022 | 4 |
| PMED BASE | 55,70 | REV0 | JUL | 07/02/2022 | 1 |
| ECMW | 55,70 | REV0 | JUL | 07/02/2022 | 2 |
| GEFS | 55,70 | REV0 | JUL | 07/02/2022 | 3 |
| ETA | 55,70 | REV0 | JUL | 07/02/2022 | 4 |
Thank you in advance,
Hope you're good.
Hello Daniel_Jesus ,
Please paste the following code into the advanced editor and follow the steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/dCsIgFMDxVxleD6Zn50zXnZUF0SBaHxey93+NzHUxS/AsEA8IP45/78VlcPtqa0cnatFR34ZxdQ8Vxul+DrdusAEJUCnYSBlOZYfwrMRUe+F2wzM6Q0wH0R3dYXw7BGC6dt53s3Edm2FkSSNiP2NYYCo3okGmSxtJG6ZLGtFoJss0Kpn5a1duBJRM993YMV3aqBWT/TYS6c9f5QKrYuMKlzSucMvGFYzbCH82Zh2jMevKjVkWGqcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MODEL = _t, AVERAGE = _t, REV = _t, MONTH = _t, DATE = _t, ITEM_N = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MODEL", type text}, {"AVERAGE", Int64.Type}, {"REV", type text}, {"MONTH", type text}, {"DATE", type datetime}, {"ITEM_N", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"REV", "MONTH"}, {{"All", each Table.Distinct(Table.Buffer(Table.Sort(_, {{"DATE", Order.Descending}})), {"MODEL"})}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"MODEL", "AVERAGE", "DATE", "ITEM_N"}, {"MODEL", "AVERAGE", "DATE", "ITEM_N"}) in #"Expanded All"Hey, Imke
Thanks for sharing your answer with me. It seems to work as I asked, but I need to make some changes. Would you mind to help me again?
Here're the changes:
1 - The table data source is an excel file, how can I read an excel file as a source in your code? Need that because everyday the data updates with new dates per REV.
2 - I committed a mistake while creating the post, in the column "AVERAGE" I have float values. But I left the commas (portuguese version) instead use ".". I'm saying that because after applying the code the values became Integer, example: 65,93 → 6593 and I need 65.93. Tried to get the right float values using the power query editor, but couldn't.
3 - Is there a way to change the date format: 04/07/2022 to 07/04/2022 (used in Brazil)? I tried in the Power Query Editor but nothing happens.
Feel free to ask for more information. Thank you again.
4 Replies
Hello Daniel_Jesus ,
Please paste the following code into the advanced editor and follow the steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/dCsIgFMDxVxleD6Zn50zXnZUF0SBaHxey93+NzHUxS/AsEA8IP45/78VlcPtqa0cnatFR34ZxdQ8Vxul+DrdusAEJUCnYSBlOZYfwrMRUe+F2wzM6Q0wH0R3dYXw7BGC6dt53s3Edm2FkSSNiP2NYYCo3okGmSxtJG6ZLGtFoJss0Kpn5a1duBJRM993YMV3aqBWT/TYS6c9f5QKrYuMKlzSucMvGFYzbCH82Zh2jMevKjVkWGqcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MODEL = _t, AVERAGE = _t, REV = _t, MONTH = _t, DATE = _t, ITEM_N = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MODEL", type text}, {"AVERAGE", Int64.Type}, {"REV", type text}, {"MONTH", type text}, {"DATE", type datetime}, {"ITEM_N", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"REV", "MONTH"}, {{"All", each Table.Distinct(Table.Buffer(Table.Sort(_, {{"DATE", Order.Descending}})), {"MODEL"})}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"MODEL", "AVERAGE", "DATE", "ITEM_N"}, {"MODEL", "AVERAGE", "DATE", "ITEM_N"}) in #"Expanded All"Hey, Imke
Thanks for sharing your answer with me. It seems to work as I asked, but I need to make some changes. Would you mind to help me again?
Here're the changes:
1 - The table data source is an excel file, how can I read an excel file as a source in your code? Need that because everyday the data updates with new dates per REV.
2 - I committed a mistake while creating the post, in the column "AVERAGE" I have float values. But I left the commas (portuguese version) instead use ".". I'm saying that because after applying the code the values became Integer, example: 65,93 → 6593 and I need 65.93. Tried to get the right float values using the power query editor, but couldn't.
3 - Is there a way to change the date format: 04/07/2022 to 07/04/2022 (used in Brazil)? I tried in the Power Query Editor but nothing happens.
Feel free to ask for more information. Thank you again.
I did some changes, after study your code better, and I accomplished what I expected. Thank you so much, Imke. Please, feel free to answer my last comment.
There are lots of ways to do this. I've written about a few methods in this community blog post:
