Filtering data with conditions

[volesh]

I have a simple dataset

Item	Item 2	Order#
apple	pear	1
pear	apple	1
orange	banana	2
banana	orange	2
pear	orange	3
orange	pear	3
banana	orange	3
orange	banana	3
banana	pear	3
pear	banana	3

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?

Item	Item 2	Order#
apple	pear	1
orange	banana	2
pear	orange	3
banana	orange	3
banana	pear	3

Someone can help?

[LivioLanzo · Accepted solution]

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

[LivioLanzo · Accepted solution]

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

[volesh]

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:

Item	Item 2	Order#	Index	Index2
apple	pear	1	1	2
pear	apple	1	2	1
orange	banana	2	3	4
banana	orange	2	4	3
pear	orange	3	5	6
orange	pear	3	6	5
banana	orange	3	7	8
orange	banana	3	8	7
banana	pear	3	9	10
pear	banana	3	10	9

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!

[LivioLanzo]

hI @volesh

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

[volesh]

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

[LivioLanzo]

@volesh, no problem! 🙂 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)

[volesh]

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

[AlB · Accepted solution]

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]

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!