Rest - Passing Parameters from Cell in Excel to Rest Query in Power Query Editor

Kinetic ERP
1

Has anyone else had any luck passing a Parameter from a cell in Excel, into Power Query? If i use the following line it works fine

api/v1/BaqSvc/HDTA-Rebate/?FiscalPeriod=9&FiscalYear=2022

I have followed any instructions i could find online for creating the Parameter but whenever i try to modify this line and run the query, where pPeriod is the Parameter that has been setup

api/v1/BaqSvc/HDTA-Rebate/?FiscalPeriod=pPeriod&FiscalYear=2022

I get the following error

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request.
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)
OData Version: 3, Error: The remote server returned an error: (400) Bad Request.
Details:
DataSourceKind=OData
DataSourcePath=https://auseastdtapp00.epicorsaas.com/saas****/api/v1/BaqSvc/HDTA-Rebate

Is what im trying to achieve possible ? Am i doing something wrong? Any help that could be provided would be useful

1 Like
2

Hi @Wayne_Carter, Iā€™m doing a presentation about this tomorrow at Insights and afterwards will post the info here on EpiUsers.help.

3

Andrew

Sounds good, If possible could you please flick up a reply once you have done this so i can check it out

4

@Wayne_Carter If understood correctly your needs. So I use this way and including API-KEY, just in case if there would be a need to disable all excels but leave working baq in epicor.

let
    date = Excel.CurrentWorkbook(){[Name="date"]}[Content]{0}[Column1], //Table for filtering 
    Company = "XXX", 
    BAQ = "Stock_Status",
    API = "XXXXXXXXXXXXXXXXXXXXXXXX", //Api Key token
    Link = "https://XXXXXXXXXXXXX.com/XXXXXXX/api/v1/BaqSvc/", //Source Link
   Source = OData.Feed(Link & BAQ & "?Company=" & Company & "&date=" & date & "&Api-Key=" & API, null, [Implementation="2.0"]) //Feed and parameter concatenation
in
    Source //Last step to load

image
image925Ɨ333 22 KB