cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MdJ83
Helper I
Helper I

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

 

MdJ83_0-1637838446414.png

 

1 ACCEPTED SOLUTION

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

14 REPLIES 14
v-jayw-msft
Community Support
Community Support

Hi @MdJ83 ,

 

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

 1.PNG

2.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

 

MdJ83_0-1638269510432.png

 

KNP
Super User
Super User

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"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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.

MdJ83
Helper I
Helper I

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

MdJ83
Helper I
Helper I

Any other suggestions?

@MdJ83  try this

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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)

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

@MdJ83  can you try this

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

 

smpa01_0-1638146116702.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


MdJ83
Helper I
Helper I

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)
MdJ83_0-1637893614875.png

 

amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors