Using Excel to Pull Data With REST and Multiple BAQs

CSmith · April 15, 2025, 11:10pm

Try this @NateS :

let   // OData Feed Pull
    Headers = [
        // Add your headers here
    ],
    // Query Parameter(s)
    partsToFind = Table.FirstValue(PartsToFind),
    baseTableData = OData.Feed(p("URI_Protocol")&"://"&p("Server_FQDN")&"/"&p("Epicor_Instance")&"/"&p("API_Version")&"/"&p("ServiceName")&"/"&p("BAQ")&"/"&"?PartsToFind="&partsToFind
        , Headers, [Implementation="2.0"]) 
in 
    baseTableData

let
	PartsToFind = Table.RemoveColumns(
			Table.TransformColumns(
					Table.AddColumn(
							Table.Group(
									Table.AddColumn(
											if(Table.IsEmpty(dt("tPartsToFind"))) then 
													#table(type table [PartNum = text], {{"Part1"},{"Part2"},{"Part3...etc"}}) // You could use this versus a named table
											else
													dt("tPartsToFind") // Use my named table called tPartsToFind
											, "GrpBy", each "EZ") // Add pseudo aggregator column GrpBy to each row
									, {"GrpBy"}, {{"PnTable", each _, type table [PartNum=text,GrpBy=text]}}) // Group into table
							, "PartsToFind", each Table.Column([PnTable], "PartNum")), // Use all of our PartNums in our pseudo group table into list
					{"PartsToFind", each Text.Combine(List.Transform(_,Text.From),"&PartsToFind="), type text}) // Aggregate each list item with a new copy of the &param= dates will need more here
			,{"GrpBy", "PnTable"}) // Remove our temp added columns to to the transposition and aggregation of the parameter(s).
in
	PartsToFind

Excel Parameter To Filter Epicor BAQ

Function p:

let
    Source = (paramName) => (Excel.CurrentWorkbook(){[Name=paramName]}[Content]{0}[Column1])
in
    Source

Function t:

let
    Source = (paramName) => (Text.From(paramName))
in
    Source

Function dt:

let
    Source = (paramName) => (Excel.CurrentWorkbook(){[Name=paramName]}[Content])
in
    Source

EDIT for explanation:
So, create a table somewhere in your workbook and note its name or set one for it. I named mine “tPartsToFind”. (TopRight of Image above) This will allow easy editing of the parameter list items using that named table which I defined and used in the Power M Query dt function provided above. I purposely had it selected to demonstrate the input params. The PartsToFind in greenish is the resultant value of the “let in” table query definition for PartsToFind and results in single concatenated row of the part numbers you can use JobNum instead

From the image you can also see the results OData Feed of the p(“BAQ”): TempEUH

Company, PartNum, Description, RowIdent see at bottom of first pic.

Any questions or further explanations please let me know.