Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MdJ83
Helper II
Helper II

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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 II
Helper II

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

MdJ83
Helper II
Helper II

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
MdJ83
Helper II
Helper II

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.