Forum Discussion

TM's avatar
TM
Icon for Advocate I rankAdvocate I
10 years ago
Solved

Comparing Sales Orders (rows) and keeping the highest version number - two column filtering

Hi,   I have got two columns, "Sales Orders" and "Version number". The sales have a correlating ID in this format: "M00123456". The data comes from an Access database. The version number is a whole...
Need Help
  • LarsSchreiber's avatar
    LarsSchreiber
    10 years ago

    Hi TM,

     

    I can only agree with itchyeyeballs: Grouping in combination with a MAX function should solve your problem. I copied your table into Excel and then pulled it into Power Query. My script looks like this 

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Change Type" = Table.TransformColumnTypes(Source,{{"Sales Order            ", type text}, {"  Version number", Int64.Type}}),
    #"Group Rows" = Table.Group(#"Change Type", {"Sales Order            "}, {{"HighestVersionNumber", each List.Max([#"  Version number"]), type number}})
in
    #"Group Rows"

    The result is the following:

     

     

     

     

     

     

    Hope that helps.

     

    Regards,

    Lars