Forum Discussion

CR's avatar
CR
Resolver II
8 years ago
Solved

remove duplicate - specific values

Hi!   I need help! I get a list of references. Each reference (column 1) has several revisions (column 2) so I get several lines for one reference: line 1 => ref_1 | rev A line 2 => ref_1 | rev ...
Need Help
  • CR's avatar
    CR
    8 years ago

    Dear Smoupre,

     

    Thanks a lot, I used the buffering and it worked !

     

    Before:

     

    let
    ...
    #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column", "DOC_REF", each Text.Combine({[Document Client Reference], Text.From([#"REV-NEW"], "fr-FR")}, "_"), type text),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Column",{{"DOC_REF", Order.Descending}}),
in
    ...

    Now:

    let
...
    #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column", "DOC_REF", each Text.Combine({[Document Client Reference], Text.From([#"REV-NEW"], "fr-FR")}, "_"), type text),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Column",{{"DOC_REF", Order.Descending}}),
    #"Buffered" = Table.Buffer(#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(#"Buffered", {"Document Reference"})
in
    #"Removed Duplicates"

    Thanks !

     

    Regards,

    CR

DAX0110's avatar
DAX0110
Resolver V
8 years ago

Hi CR, is it safe to assume that the revisions will appear in strict chronological order? 

If that's the case, and the "last revision" for each "ref_N" will always be the last row in its group,

then you can use this M script in Query Editor to retrieve them.

 

Before using this script, you need to process the table into the following form: (which you have probably already done)

 

line          ref            revision

line 1       ref_1        rev A

line 2       ref_1        rev B

line 3       ref_1        rev 0

line 4       ref_1        rev 1

line 5       ref_2        rev 0

line 6       ref_2        rev 1

 

The important points are:

(1) the 3 column names must be "line", "ref", and "revision"

(2) the table must be named "tbl" because my script depends on it as the input

 

Then, open the "Advanced Editor"  and add the following code (starting with "final = Table.Group")

to the end of the script:

 

let
    ...
    final = Table.Group(
        tbl
        , {"ref"}
        , {
            {"line", each Table.Last(_)[line], type text}
            , {"revision", each Table.Last(_)[revision], type text}
          }
        )
in
    final

 

This will output the last row of each "ref_N" group.