Join on inequality in Power Query

Question

I have been trying to answer this question

With the following data

+---------+---------+-----------+---------+
| Column1 | Column2 |  Column3  | Column4 |
+---------+---------+-----------+---------+
|       1 | happy   | 1-veggies | GHF     |
|       1 | sad     | 1-veggies | HGF     |
|       2 | angry   | 1-veggies | GHG     |
|       2 | sad     | 1-veggies | FGH     |
|       3 | sad     | 1-veggies | HGF     |
|       4 | moody   | 2-meat    | FFF     |
|       4 | sad     | 2-meat    | HGF     |
|       5 | excited | 2-meat    | HGF     |
+---------+---------+-----------+---------+

OP was asking for a way of finding how many records there were which matched 'sad' and '1-veggies', and also had another record with the same value in column 1 and a code of GHF or FGH in column 4. The first two rows qualify, but the fourth row does not qualify because (if I understand correctly) it has the correct code, but in the same record as the one matching 'sad' and '1-veggies'. The count should be one.

I think the answer would have been fairly standard if this had been a SQL question - you would do a self-join with an equality on the first column and an inequality on the row number. In SQL it would look something like this:

create table Veggies
(
  num integer,
  emotion varchar(10),
  food varchar(10),
  code varchar(10),
  seq integer
  )
  

    insert into Veggies
    values 
    (1,'happy','1-veggies','GHF',1),
    (1,'sad','1-veggies','HGF',2),
    (2, 'angry' ,'1-veggies'    ,'GHG',3),
    (2, 'sad',  '1-veggies',    'FGH',4),
    (3, 'sad',  '1-veggies',    'HGF',5),
    (4, 'moody',    '2-meat',   'FFF',6),
    (4, 'sad',  '2-meat',   'HGF',7),
    (5, 'excited',  '2-meat',   'HGF',8)
    
    with t1 (num,seq)
    as
    (
    select num,seq
    
    from veggies
      
      where emotion='sad' and food='1-veggies'
      ),
      
    t2 (num,seq)
    as
    (
    select num,seq
    
    from veggies
      
    where code='GHF' or code='FGH'
      )
      
      select *
      
      from t1 inner join t2 on t1.num=t2.num and t1.seq<>t2.seq

I thought it might be possible to do the same thing (join on first column equal but row number unequal) in Power Query, but I have worked through the steps of getting the two queries with row numbers, and am stuck here:

I don't see any way of expressing an inequality and the documentation seems unhelpful. Does anyone have any inside knowledge on how to do this?

user_3894917 · Answer

So although it looks as though you can't translate the SQL in the question directly into Power Query and replicate this in a single step

select *
      
from t1 inner join t2 on t1.num=t2.num and t1.seq<>t2.seq

you can split it into two steps as suggested by @Ron Rosenfeld.

To recap, the initial steps which hopefully were fairly straightforward were:

Establish a connection to the data as Table 1
Add an index column
Duplicate the table and call it Table 2
Filter table 1 by 'sad' and '1-veggies'
filter table 2 by 'GHF' or 'FGH'

Now join Table 2 to Table 1 using an inner join on Column 1:

and exclude rows that were in table 1 using a left anti join on the index column:

This leaves one row as required.

Join on inequality in Power Query

1 Answers