Data filtering ,sorting and removing duplicates

[Anonymous]

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

[Anonymous]

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]

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

[KNP]

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]

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]

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

[Anonymous]

Any other suggestions?

[smpa01]

@Anonymous  try this

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

[Anonymous]

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.

[smpa01]

@Anonymous on my  phone now but can you try this else I can rep later

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

[Anonymous]

No problem at all. I think I now what the problem is. It is removing the duplicates in dates (although the code does not look like its doing that) but not the order no. What I want it to do is filter on OP, then only extract the first approval date on an OP (as there are multipe dates)

[smpa01 · Accepted solution]

@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] )
        )
)

[smpa01]

@Anonymous  can you try this

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

[Anonymous]

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)

[amitchandak]

@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)