The example below has a table in rows 1 & 2 which contains a Parameter Name and Value
In Row 6 is a REST Query that uses the value in cell B2 (under value) to feed to a BAQ with a Parameter of PartNum. This then returns those values to the sheet. You can simply change the value again and click refresh to retrieve different part data. Now you could have a list of Part Numbers under value instead of just one and use some query merge functionality to provide what you are looking for.
Now I really need to figure out the security cert issue with REST & excel, I guess. By any chance does there happen to be an easy, concise guide to get this setup, for someone with no background in security certs?
Sure let’s encrypt is your friend but you’ll have to have a way to open up your epicor box briefly to the wan
Just search for “letsencrypt IIS” should find plenty of resources.
I’ve been trying to get an SSL cert with LetsEncrypt on our test server, and it’s not working for me, getting an “Name does not end in a public suffix” error.
This might be a stupid question, but does the domain need to be a registered domain?
Meaning, let’s say our internal AD domain is ABC.local and our host company domain is ABCcompany.com. Can I still use LetsEncrypt to create an SSL cert for your test Epicor server named test-erp10.ABC.local?
If not, do I have any other options?
Yes it needs to be a valid and registered TLD/Domain for it to work with LetsEncrypt
Well I guess I’ll stop fighting to get that working…
You can create a self-signed then go visit the API Help or something via Chrome.
You can then click on the Not Secure Area on the URL Bar (the red) and Click on Certificate… then it brings up a Dialog you can Export it to a File
Then on Win7 atleast If I recall you go to Internet Options and you can there import that Cert in the Trusted Root Tab, when it comes to mass scale you can prob use GPO to push that out to your local users, i am sure there is a powershell or something out there.
Thats how you can get it working internally, locally and Excel will be happy. Atleast on your dev machine.
When I look at our AD we have a few Root ones like that and alot of Intermediate Cert Authorities with Internal Server Names, so perhaps thats what internally is done… Thats a little bit out of my Windows Admin Role Scope 
I created a BAQ that pull from the Cost table and BOM table.
Then use rest data only and not have to combine using formulas.
But if you want to combine,sumifs & vlookup should be enough to combine data.
Hey @danbedwards, did you ever get around to making that example? I downloaded power query for 2013 and I can get the fill list, but I can’t seem to figure out how you do what you are showing in your screen shot.
Dan, Did you end up creating an example of this. Im having difficulty using a parameter to update the query in power query and would appreciate any help you might be able to provide
i wonder why not passing your parameter as a filter in your REST call, so the called BAQ will return only relevant data
This is exactly what im attempting to do, When i look at the parameter that is drilled down it shows exactly what i would expect (ie pPeriod = 10)
If i use the parameters
?FiscalPeriod=9&FiscalYear=2022
if i change the =9 to =pPeriod (this is my parameter name) then i get the following error returned
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
Dan
Sorry, im still having issues with this.
where doe this “value” come from?
mine looks like
/api/v1/BaqSvc/HDTA-Rebate/?FiscalPeriod=9&FiscalYear=2022", null, [Implementation=“2.0”])
I tried changing the =9 to =pPeriod
/api/v1/BaqSvc/HDTA-Rebate/?FiscalPeriod=pPeriod&FiscalYear=2022", null, [Implementation=“2.0”])
but this gives me the error above? My Parameter for pPeriod looks like the sample below that i feel is how it should look
REST-BAQ-Parameters-From-Columns.xlsx (17.9 KB)
Here is the full example, let me know if this helps.The BAQ had PartNum and Description as display columns and a parameter named PartNum.
Thanks for the file, It was enough to get me to where i needed to go.
you had the ’ " & Value & " ’ "
I realised that i didnt need the last " as i had a second parameter also and the final " goes after that one.
Living and learning
Here is the sample file I presented at Insights. You can use this to generate a full REST url complete with filters, sorting, select columns, and the number of rows to return.
EpicorQuery - zHomepage_SOOpen.xlsx (112.7 KB)
The key is a function in Power Query Editor to read the full URL from a table and cell value. In the attached Excel example it is reading the URL from the ‘Parameters1’ table, and the 11th row of the ‘Value’ column and passing that into the EpicorBAQ query.
Nice Andrew!
Dan,
I just downloaded your example and it worked like a charm.
I’ve been pulling my hair out trying to download the partmtl table for an item.
Thanks bunches!!!
Mike
