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

konstantinos's avatar
konstantinos
Icon for Memorable Member rankMemorable Member
10 years ago

 itchyeyeballs Haha!! Actually @LarsSchreiber was simpler than mine and didn't had time to imrove mine to show correct totals.

But tell us your thinking because it always useful, and if not in this usually in other problems

  • itchyeyeballs's avatar
    itchyeyeballs
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    I typed out a soultion for using the group by functionality (either in Access or power query) to create a lookup table, using the max function to only return the highest version number.

     

    May be a way to go if TM doesn't wan't the other order data in his model at all I suppose.

    • konstantinos's avatar
      konstantinos
      Icon for Memorable Member rankMemorable Member
      10 years ago

      itchyeyeballs you are right since if not using old data anywhere else, this can help navigating to data model and also performance, specially if the fact table contains millions of rows.