Forum Discussion

NathanPatel's avatar
NathanPatel
New Member
2 years ago
Solved

Identifying Similar Transactions in the same table

I am trying to identify similiar transactions in from a table.

 

For example if I had the table below Table1

 

TransactionEmployeeVendorDateAmount
1AAmazon10/1/2023 $    71.00
2BCostco

8/15/2023

 $    19.46
3CAmazon9/15/2023 $    65.46
4AAmazon10/1/2023 $    72.00
5ACostco10/1/2023 $    75.00
6BCostco9/15/2023 $    70.00
7B Costco9/16/2023 $    72.00
8AAmazon10/2/2023 $    73.00
9CAmazon9/26/2023 $    91.66
10CCostco7/4/2023 $    89.62
11CAmazon7/27/2023 $    11.29
12AAmazon9/17/2023 $    80.08
13AAmazon8/3/2023 $    10.19
14ACostco7/26/2023 $    10.94
15CWalmart9/23/2023 $    14.40

 

Would there be a way in to create a Table2  in PowerQuery that identfies matches that meet certain criteria? For example if I wanted to identify all transactions $70-$75 from the same employee to the same vendor within 10 days of each other. The second table might look like this?

 

Transaction1Transaction2
14
18
41
48
67
76
81
84

 

Any help is appreciated. RIght now I was able to make DAX functions to show if there are matching transactions for any given transactions, but I think it'd be more useful for my purposes if I was able to list out the pairs. 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    Using one of the GENERATE functions is useful here. For example,

    Similar =
GENERATE (
    FILTER (
        Transactions,
        Transactions[Amount] >= 70
            && Transactions[Amount] <= 75
    ),
    VAR _Trans = Transactions[Transaction]
    VAR _Emp = Transactions[Employee]
    VAR _Vend = Transactions[Vendor]
    VAR _Date = Transactions[Date]
    VAR _Matches_ =
        FILTER (
            Transactions,
            Transactions[Transaction] <> _Trans
                && Transactions[Employee] = _Emp
                && Transactions[Vendor] = _Vend
                && ABS ( DATEDIFF ( Transactions[Date], _Date, DAY ) ) <= 10
        )
    RETURN
        SELECTCOLUMNS ( _Matches_, "Transaction2", Transactions[Transaction] )
)

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    Using one of the GENERATE functions is useful here. For example,

    Similar =
GENERATE (
    FILTER (
        Transactions,
        Transactions[Amount] >= 70
            && Transactions[Amount] <= 75
    ),
    VAR _Trans = Transactions[Transaction]
    VAR _Emp = Transactions[Employee]
    VAR _Vend = Transactions[Vendor]
    VAR _Date = Transactions[Date]
    VAR _Matches_ =
        FILTER (
            Transactions,
            Transactions[Transaction] <> _Trans
                && Transactions[Employee] = _Emp
                && Transactions[Vendor] = _Vend
                && ABS ( DATEDIFF ( Transactions[Date], _Date, DAY ) ) <= 10
        )
    RETURN
        SELECTCOLUMNS ( _Matches_, "Transaction2", Transactions[Transaction] )
)