如何在不删除条目行的情况下从单个Power Query列中删除重复项? [英] How to remove duplicates from single Power Query column without removing entries rows?

问题描述

我有一个合并查询，我需要删除发票+税"列的重复项，而又不删除该重复项的整个行.我只需要删除该列的重复值即可.从下图可以看到，我正在尝试从发票+税"列中删除重复的值.但是我需要保留该行，只是删除重复的值，例如，我在下面突出显示了应删除的内容，未突出显示的任何内容都需要保留.此时，我还将代码包括在图片下面.

I have a merged query that I need to remove duplicates for my Invoice+Tax column without removing the entire row for that duplicate. I just need to remove the duplicate values for that one column. As you can see from the below picture I am trying to remove duplicate values from the Invoice+Tax column. But I need to keep the row, just remove the duplicate values, for example I have highlighted below what should be removed, anything not highlighted needs to remain. I have also included my code to this point below the picture.

let
Order = Order,
Source = Sql.Database("jansql01", "mas500_app"),
dbo_vdvInvoiceLine = Source{[Schema="dbo",Item="vdvInvoiceLine"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_vdvInvoiceLine,{"Description", "ItemID", "STaxClassID", "ExtAmt", "FreightAmt", "TranID", "TradeDiscAmt", "FormattedGLAcctNo", "Segment1", "Segment2", "Segment3", "SalesOrder", "CustID", "CustName", "TranDate", "PostDate", "City", "StateID", "ItemClassID", "UseTaxRate", "ReleaseSO", "Job Number"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each Text.Contains([SalesOrder], Order)),
#"Added Material Column" = Table.AddColumn(#"Filtered Rows", "Material", each if [ItemClassID] <> "INSTALLATION" then [ExtAmt] else 0),
#"Added Installation Column" = Table.AddColumn(#"Added Material Column", "Installation", each if [ItemClassID] = "INSTALLATION" then [ExtAmt] else 0),
#"Merged Queries" = Table.NestedJoin(#"Added Installation Column",{"TranID"},vdvInvoice,{"TranID"},"vdvInvoice",JoinKind.LeftOuter),
#"Expanded vdvInvoice" = Table.ExpandTableColumn(#"Merged Queries", "vdvInvoice", {"STaxAmt"}, {"vdvInvoice.STaxAmt"}),
#"Extracted Date" = Table.TransformColumns(#"Expanded vdvInvoice",{{"TranDate", DateTime.Date, type date}, {"PostDate", DateTime.Date, type date}}),
#"Added Invoice+Tax" = Table.AddColumn(#"Extracted Date", "Invoice+Tax", each [TranID]&Number.ToText([vdvInvoice.STaxAmt]))

in
#"Added Invoice+Tax"

推荐答案

我想不出这样做的任何理由，但如果您确实想这样做，请用下面的两行替换

I can't think of any reason to do it, but if you really want to, replace the bottom two rows with

#"Added Index" = Table.AddIndexColumn(#"Added Invoice+Tax", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Invoice+Tax2", each if [Index]=0 then [#"Invoice+Tax"] else if #"Added Index"{[Index]-1}[#"Invoice+Tax"]=[#"Invoice+Tax"] then null else [#"Invoice+Tax"]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns"

这篇关于如何在不删除条目行的情况下从单个Power Query列中删除重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！