Power Query - 通信ログから、同じIDの送信と受信の時間を比較する

概要

入力テーブルとして、ID毎に送信と受信が対になっている、時系列順のテーブルを用意します。
この入力テーブルから、同じIDの送受信の組み合わせをマージしたテーブルを作成する方法を考えます。

コード

作成したコードは下記のとおりです。

送受信の比較

let
    ソース = 通信ログ,
    グループ化された行 = Table.Group(ソース, {"Id", "Dir"}, {{"AllRows", each _, type table [Time=nullable number, Id=nullable text, Dir=text]}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "AddRank", each Table.AddIndexColumn([AllRows], "Rank", 1, 1)),
    #"展開された AddRank" = Table.ExpandTableColumn(追加されたカスタム, "AddRank", {"Time", "Rank"}, {"Time", "Rank"}),
    TxTable = Table.SelectRows(#"展開された AddRank", each ([Dir] = "Tx")),
    RxTable = Table.SelectRows(#"展開された AddRank", each ([Dir] = "Rx")),
    マージされたクエリ数 = Table.NestedJoin(TxTable, {"Id", "Rank"}, RxTable, {"Id", "Rank"}, "RxTable", JoinKind.FullOuter),
    #"展開された RxTable" = Table.ExpandTableColumn(マージされたクエリ数, "RxTable", {"Id", "Time"}, {"Rx.Id", "Rx.Time"}),
    #"名前が変更された列 " = Table.RenameColumns(#"展開された RxTable",{{"Time", "Tx.Time"}, {"Id", "Tx.Id"}}),
    削除された他の列 = Table.SelectColumns(#"名前が変更された列 ",{"Tx.Time", "Tx.Id", "Rx.Time", "Rx.Id"}),
    #"追加された条件列 - Sequence" = Table.AddColumn(削除された他の列, "Sequence", each if [Tx.Time] < [Rx.Time] then "TxFirst" else if [Tx.Time] > [Rx.Time] then "RxFirst" else null),
    #"追加されたカスタム - Delay" = Table.AddColumn(#"追加された条件列 - Sequence", "Delay", each Number.Abs([Tx.Time] - [Rx.Time]))
in
    #"追加されたカスタム - Delay"

Table.Groupを使って「Id」「Dir」をキーにグループ化します。
このあと「AllRows」の値を「Id」を使ってマージさせたいのですが、複数行が含まれる場合に問題となるのでもう一手間を加えます。

Table.AddIndexColumnを使って「AddRank」列を作成します。
これによって「Id」「Dir」「Rank」からマージするためのキーが一意に定まります。

「Dir」によってフィルターした「TxTable」「RxTable」を作成して、それらをマージします。

不要な列を削除してテーブルを整形します。

「Sequence」「Delay」列を計算したら完了です。