excel - 如何根据表中的一系列值过滤 Power Query？

Question

好的，所以我有一个每个月都会更改的值范围，我希望能够从这些值中筛选出 Power Query，但不能完全正确地获取代码。下面是我将表拉入 Power Query 的代码，正如您从代码下方的图片中看到的那样。正如您从该代码中看到的那样，我在 Excel 中的表名是 Job，而我要过滤掉的列是 Order。第二组代码是到目前为止我必须尝试从该表中过滤出我的查询但未成功的代码。我粘贴了查询的整个代码，但我认为它确实是 #"Filtered by Order" 行，我认为是问题所在。任何有关使该代码正常工作的帮助将不胜感激。

let
Source = Excel.CurrentWorkbook(){[Name="Job"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}})
in
#"Changed Type"

let
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", "ReleaseSO", "Job Number"}),
#"Filtered by Order" = Table.SelectRows(#"Removed Other Columns", each Table.Contains(Order,[SalesOrder = [Order]])),
#"Added Material Column" = Table.AddColumn(#"Filtered by Order", "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])),
#"Sorted Invoice+Tax" = Table.Sort(#"Added Invoice+Tax",{{"Invoice+Tax", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted 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"}),
#"Added Tax Column" = Table.AddColumn(#"Removed Columns", "Tax $", each if [#"Invoice+Tax2"] = null then 0 else [vdvInvoice.STaxAmt]),
#"Changed Tax Type" = Table.TransformColumnTypes(#"Added Tax Column",{{"Tax $", type number}}),
#"Added Total Contract" = Table.AddColumn(#"Changed Tax Type", "Total Contract $", each [#"Material $"]+[FreightAmt]+[#"Installation $"]+[#"Tax $"])
in
#"Added Total Contract"

标签： excelpowerquery

Answer 1

合并两个查询。合并命令中有一些设置，您可以单击以仅保留匹配的行。