LuckyTemplates Power Query：刪除重複行

在此博客中，我將向您展示如何根據條件刪除 LuckyTemplates Power Query 中的重複行。您可以在本博客底部觀看本教程的完整視頻。

此示例來自上提出的問題。會員有交易數據，想刪除相互抵消的行。

有兩種不同的交易類型，一種用於傳入，一種用於傳出。她還增加了運動計數。並以此為基礎，她制定了三個情景。

場景一基於兩行。如果它們相互抵消，則應刪除兩行。如果不是這種情況，則應保留這兩行。

方案二和方案三基於行的組合，部分相互抵消。這些行應該按照先進先出的原則刪除，以便只保留最新的傳入事務。

基本上，我們一遍又一遍地重複相同的邏輯。我們應該能夠設計一種方法來適應所有這些場景。

我們要做的一些事情是添加一個包含絕對數量的列，並按“添加數量餘額列”進行分組，並使用“所有行”選項添加一個嵌套表格。我們還將轉換這些嵌套表格，對移動類型和過帳日期進行排序，創建運行總計，並僅保留基於條件的行。

我將其評為中等水平，因為我們在這裡可以執行的大部分轉換都是通過使用用戶界面進行的。話雖如此，讓我們轉到 LuckyTemplates Power Query。

目錄

• 在 Power Query 中添加具有絕對數量的列
• 按 ID 索引和絕對數量分組
• 在 LuckyTemplates Power Query 中轉換嵌套表
  • 排序移動類型和過帳日期
  • 創建運行總計
  • 根據條件過濾行
• 結論

在 Power Query 中添加具有絕對數量的列

我們將首先添加一個包含絕對數量的列。選擇Quantity列，然後在Add Column功能區或選項卡上，轉到Scientific，然後單擊Absolute Value。

我將在公式欄中重命名此列。

按 ID 索引和絕對數量分組

我們現在可以按 ID 索引和絕對數量進行分組。選擇ID Index，然後按住 shift 或 control 進行多選，然後點擊Abs qty（絕對數量）。

您會在轉換選項卡上找到分組依據。

但是，您也可以在主頁選項卡上找到它。

單擊它，現在我們將添加一個新列。這不是“計數”，而是數量的“總和”。因此，讓我們將其重命名為Qty (quantity) Balance。這將是數量列的總和。然後，我們將添加另一個聚合，但它將是一個特殊的聚合，因此我們將選擇All Rows。我們還為該列命名 ( AllRows )。然後，按確定。

如果我們單擊表格中的空白區域，我們會在下方看到嵌套表格的預覽。我們看到這個嵌套表的數量相互抵消，數量餘額為零。

如果我們查看下一個嵌套表（因此在下一行的空白處單擊關閉），我們會看到數量列沒有相互抵消並且數量餘額大於零。

讓我們添加一個自定義列，以便我們稍後可以將其用於我們的嵌套表轉換。因此，選擇添加自定義列。

讓我們將該列重命名為Transformations，我們將添加零 (0) 作為佔位符。然後，單擊“確定”。

在 LuckyTemplates Power Query 中轉換嵌套表

我們現在準備創建轉換這些嵌套表的邏輯。但是我們不想寫一堆，我們也不需要。

我們可以在大多數情況下使用用戶界面。如果我們在單獨的查詢中為這些轉換構建邏輯，那麼我們遇到的最複雜的場景就是具有三行或四行的場景。

現在，如果我單擊最後一行的右側，我會看到該表有四行。因此，我們可以使用它來構建我們的轉換。

右鍵單擊空白處的一側並選擇Add as New Query。

我們看到嵌套表現在已經擴展並添加為新查詢，我們可以使用它來構建我們需要的邏輯。

排序移動類型和過帳日期

現在，我們需要做的第一件事是對Movement Type進行排序。讓我們選擇降序。這樣，傳出交易將始終位於頂部。

接下來，我們將Posting Date按升序排序，確保如果我們省略行，它將始終根據 FIFO 原則（先進先出）完成。

創建運行總計

為了確定我們要刪除的行，我將添加一個運行總計以禁用，我們可以使用 List.FirstN來完成該任務。此函數基於另一個列表創建一個列表，其中頂部項目根據特定數字或條件保留。

讓我們回到我們的查詢。對於 List.FirstN，我們需要兩件事。首先，我們需要該列表，該列表就是我們的Quantity列。如果我右鍵單擊 Quantity 列標題，我們會看到Add as NewQuery選項。

在這裡，我們看到了生成該列表所需的代碼。在應用的步驟中，您可以看到它指向最後一步，然後標識這些括號中的列。

List.FirstN 需要的第二部分是一個數字，用於標識要從該列表中保留多少數字。我們可以為此使用索引，因此我將為此行添加一 (1) 個索引列。

因此，對於數字 1，它將保留數量列中第一行的數字，依此類推。我們現在可以總結一下。

讓我們添加一個自定義列，並將其命名為Running Total。我們在這裡使用 List.FirstN，我們可以看到第一個參數是那個列表，數量列。我們需要指向最後一步，最後一步是Added Index。

我們確定了我們想要的列，那就是我們的數量列。我們的計數在索引列中。

現在它返回一個列表。如果我們單擊空白處的一側，我們可以看到該列表的內容。對於第一條記錄，它只保留了該列表的第一行。對於第二條記錄，它保留了前兩行。

現在我們需要做的就是總結這些數量，我們可以使用Lists.Sum來做到這一點。所以，我將把它添加到公式欄中。而且我還將放置Type。

根據條件過濾行

有了這個運行總計，我們就可以確定要保留的行。我們只想保留大於零的行，所以讓我們添加一個過濾條件。

我們現在可以刪除我們的輔助列。選擇 Index 和 Running Total，然後單擊 Remove Columns。

在高級編輯器中，我們現在可以復制我們創建的代碼。我將打開Advanced Editor，我們可以看到這是我們的拆分步驟。所以，我們可以選擇並複制下面的所有內容。

現在讓我們回到原來的查詢。再次打開高級編輯器。而不是佔位符，讓我們換行。我將使用“ let ”表達式，因為它可以從變量的中間計算中捕獲值。所以，“讓”然後換行並粘貼我的轉換。另外我會聲明Type。

上面突出顯示的代碼指向我們展開的嵌套表，它們位於“所有行”列中。因此，我將通過將這段突出顯示的代碼替換為AllRows來指出這一點。

所以我們的轉換列現在有嵌套表。我們基於最後一行構建查詢，對嗎？當我們開始時它包含四行並且只包含兩行。我們也有那些數量平衡為零，對嗎？現在它有一張空桌子。

我們可以通過過濾數量餘額列來刪除它們。我們不希望數量餘額為零。

然後，我將選擇我的轉換列並刪除其他列。我現在可以擴展我的轉換嵌套表。不要使用原來的列名作為前綴，然後按確定。

在 Transform 選項卡上，選擇Data Type，我們就大功告成了。這就是您清理交易數據的方式。

LuckyTemplates 中的 Unpivot 和 Pivot 基礎知識 – 查詢編輯器
在使用 LuckyTemplates 高級編輯器時查看我的實踐技巧
LuckyTemplates 中的高級轉換

結論

在本教程中，我向您展示瞭如何清理事務數據，特別是刪除重複行。這是您在使用 LuckyTemplates Power Query 時可以使用的一項很棒的技術。

我希望你喜歡這個。觀看下面的完整視頻教程了解更多詳情。查看下面的鏈接，了解有關 LuckyTemplates Power Query 編輯器的更多相關內容。

乾杯!

梅麗莎