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
volesh
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
LivioLanzo
Solution Sage
Solution Sage

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!  

View solution in original post

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

 

View solution in original post

8 REPLIES 8
LivioLanzo
Solution Sage
Solution Sage

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!  

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!

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!  

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

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

 


 


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


Proud to be a Datanaut!  

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

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

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!

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.