加入Power Query中的不平等 [英] Join on inequality in Power Query

问题描述

我一直在尝试回答

我看不到任何表示不平等的方法，文档似乎无济于事.是否有人对此有任何内在知识?

解决方案

因此，尽管看起来您无法将问题中的SQL直接转换为Power Query，也无法一步一步复制它

 选择*从t1内部联接t2到t1.num = t2.num和t1.seq t2.seq 

您可以按照@Ron Rosenfeld的建议将其分为两个步骤.

回顾一下，希望非常简单的初始步骤是:

• 按照表1建立与数据的连接
• 添加索引列
• 复制表并将其命名为表2
• 按"sad"和"1-vegies"过滤表1
• 通过"GHF"或"FGH"过滤表2

现在使用列1上的内部联接将表2联接到表1:

，并使用索引列上的左反连接排除表1中的行:

这将保留所需的一行.

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?

解决方案

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.

这篇关于加入Power Query中的不平等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！