cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
volesh Frequent Visitor
Frequent Visitor

Filtering data with conditions

I have a simple dataset

ItemItem 2Order#
applepear1
pearapple1
orangebanana2
bananaorange2
pearorange3
orangepear3
bananaorange3
orangebanana3
bananapear3
pearbanana3

as you can see, some lines are almost duplicated, so I need to get a different look of the table
I don't want to show row#2, it is enough to show row#1.
How to show the values I need?

ItemItem 2Order#
applepear1
orangebanana2
pearorange3
bananaorange3
bananapear3

Someone can help?

2 ACCEPTED SOLUTIONS

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: Filtering data with conditions

Hello @volesh

 

try like this,

 

M code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEqSE0sAlKGSrE60TAOTA4imF+UmJcO4iYl5gEhkGEEFodz4QqMkA2BixqjmgKVNsZhhjEOO9HUo5gC5SCrjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Item 2" = _t, #"Order#" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Item 2", type text}, {"Order#", Int64.Type}}),
    RemovedDuplicates = Table.Distinct(ChangedType),
    AddedIndex = Table.AddIndexColumn(RemovedDuplicates, "Index", 1, 1),
    Join = Table.NestedJoin(AddedIndex, {"Item", "Item 2", "Order#"}, AddedIndex, {"Item 2", "Item", "Order#"}, "Dupes", JoinKind.LeftOuter),
    ExpandedDupes = Table.ExpandTableColumn(Join, "Dupes", {"Index"}, {"Index2"}),
    Distinct = Table.SelectRows(ExpandedDupes, each [Index] < [Index2]),
    RemovedOtherColumns = Table.SelectColumns(Distinct,{"Item", "Item 2", "Order#"})
in
    RemovedOtherColumns

 


 


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


Proud to be a Datanaut!  

Super User
Super User

Re: Filtering data with conditions

Hi @volesh

 

I imagine it would be more convenient to do this in the query editor but if you want to do it in DAX, create a calculated table:

 

Table2 =
VAR _ResTable =
    FILTER (
        Table1;
        VAR _AuxTable =
            FILTER (
                Table1;
                Table1[Order#] = EARLIER ( Table1[Order#] )
                    && Table1[Item] = EARLIER ( Table1[Item 2] )
                    && Table1[Item 2] = EARLIER ( Table1[Item] )
            )
        RETURN
            IF (
                (
                    COUNTROWS ( _AuxTable ) <> 0
                        && Table1[Item] < Table1[Item 2]
                )
                    || COUNTROWS ( _AuxTable ) = 0;
                TRUE ();
                FALSE ()
            )
    )
RETURN
    _ResTable

 

8 REPLIES 8
LivioLanzo Super Contributor
Super Contributor

Re: Filtering data with conditions

Hello @volesh

 

try like this,

 

M code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEqSE0sAlKGSrE60TAOTA4imF+UmJcO4iYl5gEhkGEEFodz4QqMkA2BixqjmgKVNsZhhjEOO9HUo5gC5SCrjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Item 2" = _t, #"Order#" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Item 2", type text}, {"Order#", Int64.Type}}),
    RemovedDuplicates = Table.Distinct(ChangedType),
    AddedIndex = Table.AddIndexColumn(RemovedDuplicates, "Index", 1, 1),
    Join = Table.NestedJoin(AddedIndex, {"Item", "Item 2", "Order#"}, AddedIndex, {"Item 2", "Item", "Order#"}, "Dupes", JoinKind.LeftOuter),
    ExpandedDupes = Table.ExpandTableColumn(Join, "Dupes", {"Index"}, {"Index2"}),
    Distinct = Table.SelectRows(ExpandedDupes, each [Index] < [Index2]),
    RemovedOtherColumns = Table.SelectColumns(Distinct,{"Item", "Item 2", "Order#"})
in
    RemovedOtherColumns

 


 


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


Proud to be a Datanaut!  

Super User
Super User

Re: Filtering data with conditions

Hi @volesh

 

I imagine it would be more convenient to do this in the query editor but if you want to do it in DAX, create a calculated table:

 

Table2 =
VAR _ResTable =
    FILTER (
        Table1;
        VAR _AuxTable =
            FILTER (
                Table1;
                Table1[Order#] = EARLIER ( Table1[Order#] )
                    && Table1[Item] = EARLIER ( Table1[Item 2] )
                    && Table1[Item 2] = EARLIER ( Table1[Item] )
            )
        RETURN
            IF (
                (
                    COUNTROWS ( _AuxTable ) <> 0
                        && Table1[Item] < Table1[Item 2]
                )
                    || COUNTROWS ( _AuxTable ) = 0;
                TRUE ();
                FALSE ()
            )
    )
RETURN
    _ResTable

 

volesh Frequent Visitor
Frequent Visitor

Re: Filtering data with conditions

Hi @LivioLanzo,
I checked your code and have to say - it doesn't work correctly. Or I made some mistakes...
Unfortunately, I have something like that:

ItemItem 2Order#IndexIndex2
applepear112
pearapple121
orangebanana234
bananaorange243
pearorange356
orangepear365
bananaorange378
orangebanana387
bananapear3910
pearbanana3109

And the condition 

Table.SelectRows(ExpandedDupes, each [Index] < [Index2]),

works correctly but without the result that I need.
Anyway, Thanks a lot for your help!

volesh Frequent Visitor
Frequent Visitor

Re: Filtering data with conditions

Thanks, @AlB,
It works!
You are right it would be better to make this in the query editor, but all I tried - did not work correctly.
Your DAX is too difficult but I try to understand and will use your solution.
Thanks a lot!

LivioLanzo Super Contributor
Super Contributor

Re: Filtering data with conditions

hI @volesh

 

this is weird because when i run it on my side this is what I see as a result:

 

Capture.PNG

 


 


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


Proud to be a Datanaut!  

volesh Frequent Visitor
Frequent Visitor

Re: Filtering data with conditions

@LivioLanzo,
I believe you and will check it again!

LivioLanzo Super Contributor
Super Contributor

Re: Filtering data with conditions

@volesh, no problem! Smiley Happy I was actually curious why you'd get wrong results on your side

 

by the way, are you extracting the data from a database? If so, you can do this even faster directly with SQL, you would need to change the name of the database table which I have put in red:

 

WITH
C1 AS
( SELECT 
	f.item, 
	f.item2, 
	f.orderid, 
	ROW_NUMBER() OVER(PARTITION BY f.orderid ORDER BY (SELECT NULL)) AS Indx
   FROM fruits AS f ),
C2 AS
(select
	f1.Item,
	f1.Item2,
	f1.ORDERID,
	f1.Indx,
	f2.Indx AS Indx2
FROM C1 AS f1 
LEFT JOIN C1 AS f2
ON f1.Item = f2.Item2 AND f1.Item2 = f2.Item AND f1.ORDERID = f2.ORDERID) SELECT C2.Item, C2.Item2, C2.ORDERID FROM C2 WHERE (C2.Indx < C2.Indx2) OR (C2.Indx2 IS NULL)

 


 


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


Proud to be a Datanaut!  

volesh Frequent Visitor
Frequent Visitor

Re: Filtering data with conditions

@LivioLanzo Hello,
I am so sorry, you were right and I had a mistake...
your code

   ExpandedDupes = Table.ExpandTableColumn(Join, "Dupes", {"Index"}, {"Index2"}),
    Distinct = Table.SelectRows(ExpandedDupes, each [Index] < [Index2]),

and my

Distinct = Table.SelectRows(ExpandedDupes, each "Index" < "Index2"),

 About SQL, query it is much easier but I can't use a direct query, have to use data model )
Thanks @LivioLanzo
Have a nice day!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 316 members 3,137 guests
Please welcome our newest community members: