LuckyTemplates Power Query: 重複する行の削除

このブログでは、条件に基づいて LuckyTemplates Power Query の重複行を削除する方法を説明します。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

で提起された質問からのものです。メンバーにはトランザクション データがあり、互いに打ち消し合う行を削除したいと考えています。

トランザクションには 2 つの異なるタイプがあり、1 つは受信用、もう 1 つは送信用です。移動回数も追加しました。そしてそれに基づいて、彼女は 3 つのシナリオを策定しました。

シナリオ 1 は 2 つの行に基づいています。それらが互いに打ち消し合う場合は、両方の行を削除する必要があります。そうでない場合は、両方の行を保持する必要があります。

シナリオ 2 と 3 は行の組み合わせに基づいており、部分的に相互に打ち消し合います。これらの行は、最新の受信トランザクションのみが保持されるように、先入れ先出しベースで削除する必要があります。

基本的に、同じロジックを何度も繰り返しています。これらすべてのシナリオに適合する単一のアプローチを設計できるはずです。

これから行うことのいくつかは、絶対数量を含む列を追加し、「数量バランス列を追加」してグループ化し、「すべての行」オプションを使用してネストされたテーブルを追加することです。また、これらのネストされたテーブルを変換し、移動タイプと転記日を並べ替え、累計を作成し、条件に基づいて行のみを保持します。

ここで実行できる変換のほとんどはユーザー インターフェイスを使用するため、これを中レベルと評価しました。そうは言っても、LuckyTemplates Power Query に移りましょう。

目次

• パワークエリに絶対量を含む列を追加する
• ID インデックスと絶対数量でグループ化
• LuckyTemplates Power Query での入れ子になったテーブルの変換
  • 移動タイプと転記日の並べ替え
  • 累計の作成
  • 条件に基づいて行をフィルタリングする
• 結論

パワークエリに絶対量を含む列を追加する

まず、絶対量を含む列を追加します。[数量]列を選択し、[列の追加]リボンまたはタブで[科学]に移動し、[絶対値]をクリックします。

数式バーでこの列の名前を変更します。

ID インデックスと絶対数量でグループ化

ID インデックスと絶対量でグループ化できるようになりました。ID Indexを選択し、Shift または Control を押しながら複数選択し、Abs qty (絶対数量) をクリックします。

[グループ化] は [変換] タブにあります。

ただし、[ホーム] タブからも見つけることができます。

それをクリックすると、新しい列を追加します。「数」ではなく、数量の「合計」になります。そこで、名前をQty (数量) Balanceに変更しましょう。数量欄の合計となります。次に、別の集計を追加しますが、これは特別なものになるため、 [すべての行]を選択します。この列に名前 ( AllRows ) も付けましょう。次に、「OK」を押します。

テーブル内の空白部分をクリックすると、下にネストされたテーブルのプレビューが表示されます。このネストされたテーブルの数量が互いに相殺され、数量残高がゼロになっていることがわかります。

次のネストされたテーブルを見ると (次の行の空白の横をクリックします)、数量列が互いに打ち消し合わず、数量残高が 0 より大きいことがわかります。

カスタム列を追加して、後でネストされたテーブルの変換に使用できるようにしましょう。そこで、「カスタム列の追加」を選択します。

その列の名前を「Transformations」に変更し、プレースホルダーとしてゼロ (0) を追加しましょう。次に、「OK」をクリックします。

LuckyTemplates Power Query での入れ子になったテーブルの変換

これで、ネストされたテーブルを変換するロジックを作成する準備が整いました。しかし、それらを大量に書きたくはありませんし、その必要もありません。

ほとんどの場合、ユーザー インターフェイスを使用できます。これらの変換のロジックを別のクエリで構築すると、最も複雑なシナリオは 3 行または 4 行のシナリオになります。

ここで、最後の行の右側をクリックすると、このテーブルには 4 つの行があることがわかります。したがって、これを使用して変換を構築できます。

空白部分の側面を右クリックし、[新しいクエリとして追加]を選択します。

ネストされたテーブルが展開され、新しいクエリとして追加されていることがわかります。これを使用して必要なロジックを構築できます。

移動タイプと転記日の並べ替え

さて、最初に行う必要があるのは、Movement Typeでソートすることです。降順を選択しましょう。こうすることで、送信トランザクションが常に最上位に表示されます。

次に、投稿日を昇順に並べ替えて、行を省略した場合でも常に FIFO 原則 (先入れ先出し) に従って実行されるようにします。

累計の作成

削除する行を識別するために、無効にする累計を追加します。そのタスクにはList.FirstNを使用できます。この関数は、別のリストに基づいてリストを作成し、上位の項目が特定の数値または基準に基づいて保持されます。

クエリに戻りましょう。List.FirstN には 2 つのことが必要です。まず、そのリストが必要です。そのリストは数量列です。[数量] 列ヘッダーを右クリックすると、 [NewQuery として追加]オプションが表示されます。

ここに、そのリストを生成するために必要なコードが表示されます。適用されたステップでは、最後のステップを指しており、括弧内の列を識別していることがわかります。

List.FirstN に必要な 2 番目の部分は、そのリストからいくつの数値を保持するかを識別する数値です。これにはインデックスを使用できるので、この行に 1 からインデックス列を追加します。

したがって、数値 1 については、[数量] 列の最初の行の数値が維持されます。そして今、それを要約することができます。

カスタム列を追加して、「Running Total」という名前を付けてみましょう。ここでは List.FirstN を使用します。最初のパラメーターがそのリスト、つまり Quantity 列であることがわかります。最後のステップを指す必要があります。その最後のステップは追加されたインデックスです。

必要な列が特定されました。それが「数量」列です。カウントはインデックス列にあります。

これでリストが返されるようになりました。白いスペースの横をクリックすると、リストの内容が表示されます。最初のレコードについては、リストの一番上の行のみが保持されました。2番目の記録では、上位2行を維持しました。

あとはこれらの数量を合計するだけです。Lists.Sum を使用してそれを行うことができます。そこで、数式バーにそれを追加します。Typeも入れます。

条件に基づいて行をフィルタリングする

この現在までの合計を使用して、保持したい行を特定できます。ゼロより大きい行のみを保持したいので、フィルター条件を追加しましょう。

これでヘルパー列を削除できるようになりました。[インデックスと累計] を選択し、[列の削除] をクリックします。

Advanced Editor で、作成したコードをコピーできるようになりました。Advanced Editorを開くと、これが分割ステップであることがわかります。したがって、その下にあるすべてのものを選択してコピーできます。

ここで、元のクエリに戻りましょう。もう一度、詳細エディターを開きます。プレースホルダーの代わりに、新しい行に移動しましょう。「 let 」式を使用すると、中間計算の値を変数に取り込むことができるためです。そこで、「let」して新しい行に移動し、変換を貼り付けます。また、 Typeを宣言します。

上記の強調表示されたコードは、[すべての行] 列内に配置される、展開されたネストされたテーブルを指します。そこで、この強調表示されたコードをAllRowsに置き換えることで、そのことを指摘します。

したがって、変換列にはネストされたテーブルが含まれています。最後の行に基づいてクエリを作成しますよね? 開始時には 4 行含まれていましたが、2 行しか含まれていませんでした。それらの数量のバランスもゼロですよね? そして今、空のテーブルがあります。

これらは、「数量残高」列でフィルタリングすることで削除できます。数量残高がゼロになることは望ましくありません。

次に、変換列を選択し、他の列を削除します。これで、変換ネストされたテーブルを展開できるようになりました。元の列名をプレフィックスとして使用しないで、「OK」を押します。

[変換] タブで[データ型]を選択すると、すべて完了です。これがトランザクション データをクリーンアップする方法です。

LuckyTemplates のアンピボットとピボットの基本 – クエリ エディター
LuckyTemplates アドバンスト エディターを使用する際の実践的なヒントを確認する
LuckyTemplates の高度な変換

結論

このチュートリアルでは、トランザクション データをクリーンアップする方法、特に重複行を削除する方法を説明しました。これは、LuckyTemplates Power Query を利用するときに使用できる優れたテクニックです。

楽しんでいただければ幸いです。詳細については、以下の完全なビデオチュートリアルをご覧ください。LuckyTemplates Power Query エディターに関するその他の関連コンテンツについては、以下のリンクも確認してください。

乾杯！

メリッサ