Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

keep the most recent record

dear all

 

I'm starting with PowerBI//PowerQuery. I will push data from an ERP to make dashboards. I have a specific case that is the following:

* i will get a file every week that extract data from the current month.

 

I neeed to store this lines and will have duplicated rows. but in some cases, the "duplicates row" means it is the same order but with updated fields. So I need to keep only the most recent field. I have made a key and have the date. I need just to know how to keep only the most recent.

 

here's a sample :

 

 

In this sample, I have 2 lines with the same Key (field's name is CLE). I need to keep the most recent.

thank you

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcoxDoAgDAXQq5DOkPTXoNIN1E1PQLj/NexmBKa3vFpJWCTwFgTO7RqTxtXlhzzdYGYzU/P/lpQxtNI3W6KQb8E8+gXM2jlpy9guau0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date modified" = _t, CLE = _t, #"Type BL" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date modified", type datetime}, {"CLE", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CLE"}, {{"Max", each List.Max([Date modified]), type nullable datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"CLE", "Date modified"}, #"Grouped Rows", {"CLE", "Max"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"CLE"}, {"CLE.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Grouped Rows", each ([CLE.1] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"CLE.1"})
in
    #"Removed Columns"

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcoxDoAgDAXQq5DOkPTXoNIN1E1PQLj/NexmBKa3vFpJWCTwFgTO7RqTxtXlhzzdYGYzU/P/lpQxtNI3W6KQb8E8+gXM2jlpy9guau0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date modified" = _t, CLE = _t, #"Type BL" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date modified", type datetime}, {"CLE", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CLE"}, {{"Max", each List.Max([Date modified]), type nullable datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"CLE", "Date modified"}, #"Grouped Rows", {"CLE", "Max"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"CLE"}, {"CLE.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Grouped Rows", each ([CLE.1] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"CLE.1"})
in
    #"Removed Columns"