@Kpearson Yes, you got the feed pull there in your image. Looks like the Power Query Editor UI and that is what you will need.
- Add the p function I provided above in the Editor there. Optional: Add the t and dt functions as well.
- Create a named cell above your table or somewhere else on the sheet utilizing that query so it makes sense where the viewer provides it.
- Either COPY current query and edit to new table.
- Edit the Query feed to be like the code below
You need to have the BAQ copied to a new one with the parameter you would like if you want to simplify it OR you can use the power M query transform to use parameter and filter your data set with is like this:
Add the Table.SelectColumns method around your OData.Feed like this:
= Table.SelectRows(OData.Feed(%Your SERVER URL REST CALL% or p("My REST Feed URL in a named CELL")), each ([JobHead_JobNum] = p("Named Cell Parameter Name for Job Head")))
I do the similar above in the advanced editor and I provided to correct code for that editor in my post above. The p function code I provided will dynamically use a named cell/range and provide the VALUE of that CELL/RANGE within a Power M query (or even other Excel functions which I do not show/explain here). Much easier than hopping back to this Editor interface when a parameter/filter value needs to change.
***Assumption: Your table column name is “JobHead_JobNum”
CAVEAT: This is not the best or most efficient way to do this! Better would be to duplicate your original BAQ to a new query with parameter to filter the result set during the OData.Feed refresh, or since you already have the data filter the table set using the dt function and a transform using something like mine 1st and possible yours 2nd/3rd:
/* Copied from MINE Advanced Editor with additional transform to tweak column data type so it comes out more excel friendly */
let
Source = Table.SelectRows( Table.TransformColumnTypes(dt("TrailerCostingData"),{{"Site CostID", Int64.Type}, {"Product Group", type text}, {"Trailer", type text}, {"Trailer Description", type text}, {"Option Type", type text}, {"Option PartNum", type text}, {"Config Desc", type text}, {"Option Description", type text}, {"Is Default Option", type logical}, {"Inc Cost", type number}, {"Item Total Cost", type number}, {"Trailer Model", type text}}), each [Product Group]=p("SelectedCategory"))
in
Source
/* Example of shorthand for yours */
=Table.SelectRows(dt("Named Table Range for your Material Query"), each ([JobHead_JobNum] = p("Named Cell Parameter Name for Job Head")))
/* Advanced Editor result of shorthand above */
let
Source = Table.SelectRows(dt("Named Table Range for your Material Query"), each ([JobHead_JobNum] = p("Named Cell Parameter Name for Job Head")))
in
Source
The dt function provides linking back to already available named tables in Excel workbook and using them in a new query or a subset of data like I have in mine.
Multiple Dynamic lists combo boxes provide filtering and trigger VBA macros to refresh the linked data set.
'VBA Code on worksheet used named ranges as triggers to update (Power M Query -> Table) Dynamic Lists on hidden tab used as data validation list for named cells providing data/trigger to build the Quote vs Costing worksheet info
Private Sub Worksheet_Change(ByVal Target As Range)
' Detect cell change and refresh relevant table(s)
Dim oldText As String
Dim tbl As Variant
Select Case Target.Address
Case Range("SelectedModel").Address
' Model Changed
oldText = Range("StatusDisplay").Text
PricingTool.Unprotect
Range("StatusDisplay").Value = "One moment updating Trailer Options . . ."
Application.StatusBar = Range("StatusDisplay").Value
ActiveWorkbook.Connections("Query - TrailerOptions").Refresh
PricingTool.Unprotect
Range("SelectedModel").Select
Range("StatusDisplay").Value = oldText
Range("ModelTrailerConfig").ClearContents
Set tbl = PricingTool.ListObjects("TrailerOptions").TableObject.ListObject
Application.EnableEvents = False
If tbl.ListRows.Count > 0 Then
tbl.DataBodyRange.Sort _
Header:=xlYes _
, Key1:=tbl.ListColumns("Option/Standard").DataBodyRange _
, Order1:=xlDescending _
, Key2:=tbl.ListColumns("Option Type").DataBodyRange _
, Order2:=xlAscending
Application.EnableEvents = True
End If
PricingTool.Protect
Case Range("SelectedCategory").Address
' Category Changed
oldText = Range("StatusDisplay").Text
PricingTool.Unprotect
Range("StatusDisplay").Value = "One moment updating Trailer Model dropdown . . ."
Application.StatusBar = Range("StatusDisplay").Value
Range("ModelTrailerConfig").ClearContents
Range("SelectedModel").ClearContents
ActiveWorkbook.Connections("Query - dlSelectedProductGroupTrailers").Refresh ' This is to refresh a dynamic list attached to the cell validation
Range("SelectedModel").Select
PricingTool.Unprotect
Range("StatusDisplay").Value = oldText
PricingTool.Protect
End Select
Application.StatusBar = ""
Application.EnableEvents = True
Set tbl = Nothing
End Sub
' VBA module code to refresh ALL data tables
Public Sub EzTrailersDataRefreshAll()
Dim oldText, statusText As String
statusText = "One moment updating datasets using data below. . ."
PricingTool.Select
oldText = PricingTool.Range("StatusDisplay").Text
PricingTool.Range("StatusDisplay").Value = statusText
Application.StatusBar = statusText
EzTrailers.RefreshAll
Application.StatusBar = ""
PricingTool.Range("StatusDisplay").Value = oldText
End Sub
'Callback for btnEzRefreshData onAction
Public Sub aRefreshData(control As IRibbonControl)
PricingTool.Unprotect
EzTrailersDataRefreshAll
PricingTool.Protect
End Sub
Only one of the 8 profiles though 
