How to remove specific rows based on a condition?

[Daniel_Jesus]

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.

[ImkeF · Accepted solution]

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"

[AlexisOlson]

There are lots of ways to do this. I've written about a few methods in this community blog post:

Select Distinct Rows Ordered by Another Column

[Daniel_Jesus · Accepted solution]

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.

[Daniel_Jesus]

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.

[ImkeF · Accepted solution]

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"