Forum Discussion

EZimmet's avatar
EZimmet
Icon for Resolver I rankResolver I
1 year ago
Solved

Need to pull the most current record from a list

Good Day 

In my table I have a few records showing multiple lines - in the sample data below column EntID is one record that was updated 5 times,   I need to pull the EntID's with the highest NOrder value.  

guessing some type of a grouping looking for the MAX just never have I done this type prior - TY   

 

 

ID

EntID

NOrder

Vendor

5923

1201978

1

null

9137

1201978

2

973318

9151

1201978

3

973318

9202

1201978

4

973318

9216

1201978

5

973999

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

    Create a calculated table using DAX :

    LatestRecords = 
FILTER(
    'Table',
    'Table'[NOrder] = 
    CALCULATE(
        MAX('Table'[NOrder]),
        ALLEXCEPT('Table', 'Table'[EntID])
    )
)

     

     or using PQ :

    let
    Source = Csv.Document(File.Contents("EntID_highest_NOrder.csv"), [Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"ID", Int64.Type}, {"EntID", Int64.Type}, {"NOrder", Int64.Type}, {"Vendor", Int64.Type}}),
    #"Added MaxNOrder" = Table.AddColumn(#"Changed Type", "MaxNOrder", each List.Max(List.Transform(Table.SelectRows(#"Changed Type", each [EntID] = _[EntID])[NOrder], each _))),
    #"Filtered Rows" = Table.SelectRows(#"Added MaxNOrder", each [NOrder] = [MaxNOrder]),
    #"Removed MaxNOrder" = Table.RemoveColumns(#"Filtered Rows", {"MaxNOrder"})
in
    #"Removed MaxNOrder"

     

     

3 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    1 year ago

    Create a calculated table using DAX :

    LatestRecords = 
FILTER(
    'Table',
    'Table'[NOrder] = 
    CALCULATE(
        MAX('Table'[NOrder]),
        ALLEXCEPT('Table', 'Table'[EntID])
    )
)

     

     or using PQ :

    let
    Source = Csv.Document(File.Contents("EntID_highest_NOrder.csv"), [Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"ID", Int64.Type}, {"EntID", Int64.Type}, {"NOrder", Int64.Type}, {"Vendor", Int64.Type}}),
    #"Added MaxNOrder" = Table.AddColumn(#"Changed Type", "MaxNOrder", each List.Max(List.Transform(Table.SelectRows(#"Changed Type", each [EntID] = _[EntID])[NOrder], each _))),
    #"Filtered Rows" = Table.SelectRows(#"Added MaxNOrder", each [NOrder] = [MaxNOrder]),
    #"Removed MaxNOrder" = Table.RemoveColumns(#"Filtered Rows", {"MaxNOrder"})
in
    #"Removed MaxNOrder"

     

     

    • EZimmet's avatar
      EZimmet
      Icon for Resolver I rankResolver I
      1 year ago

      worked like a charm - TY 

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 year ago

    EZimmet 

    if you want to do grouping, that you must have a column that contains same value. 

     

    you can create an column in PQ to get the max order

     

    = Table.AddColumn(Source, "Custom", each List.Max(Table.SelectRows(Source,(x)=>x[record]=[record])[NOrder])=[NOrder])

     

     

    then you can filter by the new column

     

    or you can create a column by using DAX

     

    Column = 'Table'[NOrder]=CALCULATE(max('Table'[NOrder]),ALLEXCEPT('Table','Table'[record]))
     
    or create a table 
     
    Table 2 =
    var tbl=FILTER(ADDCOLUMNS('Table (2)',"CHECK",if('Table (2)'[NOrder]=CALCULATE(max('Table (2)'[NOrder]),ALLEXCEPT('Table (2)','Table (2)'[record])),1)),[CHECK]=1)
    return SELECTCOLUMNS(tbl,"record",[record],"ID",[ID],"EntID",'Table (2)'[EntID],"NOrder",'Table (2)'[NOrder],"Vendor",'Table (2)'[Vendor])
    '
     
    pls see the attachment below