Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering data with conditions

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 ba...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hello Anonymous

     

    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
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous

     

    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

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

LivioLanzo,
I believe you and will check it again!

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Anonymous, 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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!