合并多行日期的查询匹配 [英] Merge Query Matching on Dates in Multiple Rows

问题描述

我正在尝试合并 Power BI Desktop 中的 2 个查询，将一个查询中基于用户和日期列的行与另一个查询中的行进行匹配，其中用户匹配并且第二个查询中的日期最接近第一个查询中日期之前的一个.

I'm trying to merge 2 queries in Power BI Desktop, matching rows based off a user and date column in one query to a row in the other query, where the user matches and the date in the 2nd query is the closest one before the date in the 1st query.

在我需要匹配多个列的其他场景中，我通常会创建一个组合键来匹配，但这里不是直接匹配.

In other scenarios I need to match on more than one column, I'll usually create a composite key to match, but here's it's not a direct match.

2 个查询的示例是:

QUERY1

User     Activity     Activity Date
User 1   Activity 1   2019-01-24
User 1   Activity 2   2019-03-03
User 1   Activity 3   2019-04-17

QUERY2

User     Status     Status Change Date
User 1   Status 1   2019-02-05
User 1   Status 2   2019-03-06
User 1   Status 3   2019-04-05

我正在寻找的合并查询是:

And the merged query I'm looking for is:

合并查询

User     Activity     Activity Date   Status
User 1   Activity 1   2019-01-24       
User 1   Activity 2   2019-03-03      Status 1
User 1   Activity 3   2019-04-17      Status 3

这两个查询均来自 REST API.如果它是 SQL 源，我会使用 SQL 查询创建基于 Query2 的开始和停止日期的派生岛表，并针对 Query1 执行 BETWEEN 连接，并将其作为 Power BI 的源.

Both queries are sourced from a REST API. If it was a SQL source, I'd use a SQL query to create a derived island table of start and stop dates based on Query2 and do a BETWEEN join against Query1 and have that be the source for Power BI.

在 Power Query 编辑器中，如何获得合并的查询结果?

Within the Power Query Editor, how would I get to the merged query result?

推荐答案

首先，您希望按照您的建议进行操作并修改状态表，使其具有开始和停止日期，而不是 Status Change Date.你可以通过排序、索引和自我合并来做到这一点，正如我之前解释的 此处 和此处.

First, you want to do as you suggested and modify the status table to have start and stop dates instead of Status Change Date. You can do this by sorting, indexing, and self-merging as I've previously explained here and here.

一旦你有了它，你就可以在每一行中加载一份状态表的副本，并使用 User 和 Date 列来过滤表，最后返回一个Status 的值.

Once you have that, you can load a copy of the status table in each row and use the User and Date columns to filter the table and finally return a single value for Status.

let
    Source = <Query1 Source>
    #"Added Custom" =
    Table.AddColumn(Source, "Status",
        (C) => List.First(
                   Table.SelectRows(Status, 
                       each [User] = C[User] and 
                            [Start] < C[Date] and
                            ([Stop] = null or C[Date] <= [Stop])
                   )[Status]
               ),
        type text)
in
    #"Added Custom"

这表示我们采用 Status 表并对其进行过滤，以便根据当前行 User 匹配并且 Date 介于 Start 和 Stop.从过滤后的表中，我们选择 Status 列，这是一种列表数据类型，因此我们选择列表的第一个元素来获取列表中唯一成员的文本值.

This says we take the Status table and filter it so that based on the current row the User matches and the Date is between Start and Stop. From that filtered table, we select the Status column, which is a list data type, so we pick the first element of the list to get the text value of the only member of the list.

这篇关于合并多行日期的查询匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！