Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Data filtering ,sorting and removing duplicates

I tried to look into a couple of place but a bit stuck.

 

I'm trying to create a new table from existing data by filtering certain colomns, but also then want to sort the approval date assending and then remove dupllicates based on order no. so that it keeps the first and remove the next.

 

This is what the data and my formula looks like

 

 

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  I have tested out the code I gave you and it should meet your expectation

    Table 2 =
GROUPBY ( --groupby gets the filtered table with only OP and gives the MAX of Approval
         -- by Order Type and Order No, thereby eliminating any duplicates whatsoever
    FILTER ( 'Table', 'Table'[Order Type] = "OP" ), --flters only OP
    [Order Type],
    [Order No],
    "Approval", MAXX ( CURRENTGROUP (), [Approval] )
)

     

    If the above does not help, please try this one

    Table 4 =
SUMMARIZECOLUMNS (
    'Table'[Order Type],
    'Table'[Order No],
    FILTER ( 'Table', 'Table'[Order Type] = "OP" ),
    "Approval",
        CALCULATE (
            MAX ( 'Table'[Approval] ),
            ALLEXCEPT ( 'Table', 'Table'[Order No] )
        )
)

     

14 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    I always prefer to do this kind of modelling in Power Query.

    If you're open to that solution, see attached PBIX and below code.

    let
    Source = Orders,
    #"Filtered Rows" = Table.SelectRows(Source, each ([OrderType] = "OP")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"OrderNumber", Order.Ascending}, {"Approval", Order.Ascending}}),
    BufferToForceSorting = Table.Buffer(#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(BufferToForceSorting, {"OrderType", "OrderNumber"})
in
    #"Removed Duplicates"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The problem is a have done a lot of data manipulations in DAX that I wont show obviously in PowerQuery as it will be pretty easy to do this in power query with filter/sort/ remove duplicates.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Add a column to raw table:

    _fist = CALCULATE(MIN('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[NO.]),'Table'[type]="OP"))

    Create a new table:

    Table 2 = FILTER('Table','Table'[date]='Table'[_fist])

     

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks so much, great idea of doing that. One problem is that in my data there is multiple PO's with the same date so it is filtering out the addtional date but still left with duplicates. I need only a single value as I'm using this in lookupvalue

       

       

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try to add rank column on top of it and filter for 1

    filter(ADDCOLUMNS(<Your code>, "Rank", Rankx(filer('Order',[Order no] = earlier([Order no])),[Approval Date]),,asc,dense) , [Rank]=1)

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This does not seem to works as it does not recognised filer

     

    Approval Date OS = filter(ADDCOLUMNS((SELECTCOLUMNS(CALCULATETABLE( 'Order',FILTER('Order','Order'[Order Type]="OP")),"Order Type",'Order'[Order Type],"Order No",'Order'[Order No.],"Approval",'Order'[Approval Date])), "Rank", Rankx(filer('Order',[Order no] = earlier([Order no])),[Approval Date]),,asc,dense) , [Rank]=1)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Any other suggestions?

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous  can you try this

      Table =
GROUPBY (
    tbl,
    tbl[OrderType],
    tbl[Approval],
    "Order No", MAXX ( CURRENTGROUP (), tbl[Order No] )
)

       

       

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous  try this

       

      Table = GROUPBY ( filter(tbl,tbl[OrderType]="OP"), tbl[OrderType], tbl[Approval], "Order No", MAXX ( CURRENTGROUP (), tbl[Order No] ) )

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks for that but for some reason that is filtering my data from 9m rows to 1400 rows whilst I would expect at least 6m after the filter. Also it did not remove duplicate order numbers.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thas seems to work, although i want to filter to only show in Order Type = OP