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 an...
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] )
        )
)

     

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.

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

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