CODEWITHSUNDEEP
excelpowerquery
Jamie Walker
Jamie Walker

Reputation: 213

How to filter Power Query based off of a range of values in a table?

Ok, so I have a range of values that will change every month and I would like to be able to filter a Power Query off of these but can't quite get the code right. Below is the code I have for pulling the table into Power Query and that works as you can see from the picture below the code. As you can see from that code my table name in Excel is Job and the column I am filtering off of is Order. The second set of code is the code I have to try to filter my query off of this table unsuccessfully thus far. I pasted the entire code for the query but it's really the #"Filtered by Order" line I believe is where the issue is. Any help on getting that code to work would be greatly appreciated.

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

enter image description here

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"

Upvotes: 0

Views: 1539

Answers (2)

xChillout
xChillout

Reputation: 105

teylyn's answer is a good and possible approach.

Another way to approach this, may be passing the Order numbers of the Job table as a list into the filter argument in the second query:

Orderlist = Job[Order]
.
.
.
#"Filtered by Order" = Table.SelectRows(#"Removed Other Columns", each List.Contains(Orderlist,[SalesOrder])),

Upvotes: 0

teylyn
teylyn

Reputation: 35990

Merge the two queries. There are settings in the Merge command that you can click to keep only matching rows.

Upvotes: 2

Related Questions