Before we get into it, see if it will let you connect via http:// instead of https://
Access to this resource is forbidden.
I also tried it in Postman and it was a no-go.
Are you just wanting to retrieve a BAQ, or wanting to do more complicated stuff?
If it’s simple, could probably whip up a little proxy you could serve on IIS to call it, and pass the data back over http. If it’s more complex I don’t know if I’d do that. Definitely not outside the local net 
I’m not even entirely sure of the scope. I was trying to do a proof of concept. I’ve been writing direct queries for awhile but it’s super annoying when we make a change to a BAQ and then I have to remember to update the queries in Power BI.
It seems like it should be so simple.
You absolutely need to be using a signed certificate to connect to REST services on both Excel and PowerBI. You also need to setup your connection as a generic “Web Service” and not “oData Stream”. This is because the OData connector doesn’t let you specify additional headers for your requests, and you need to supply the API key…
So, once you have a signed cert (free one from here https://letsencrypt.org/), create a Web connection, go in advanced mode, and at the bottom you will have a section for additonal headers. You need to add the following headers:
X-API-Key: [API Key from Kinetic]
Authorization: Basic [username:password, encoded as Base64]
For PowerBI I would recommend you use variables for the API key and auth, so you don’t have to repeat it everywhere…
1 Like
I tried to go here but I am lost. What’s shell? Even when I pick one or the other, I don’t see anything to download…
What do you mean variables? I don’t follow.
We have a nice thread on here somewhere where we hammered that out.
Go here specifically for a signed wildcard certificate you can use on your entire domain:
For PowerBI variables, look here:
Thank you. I think I can follow that.
I think this is the thread:
1 Like
Actually the instructions for Certbot are a little obtuse, because they don’t list walkthrough for IIS… Try this link instead:
1 Like
That’s the one. I’ve been in lurk mode on this thread as it’s stuck behind the cert issue. Once that’s sorted I’ll keep an eye open for Power Query/BI M language questions to pounce on. 
1 Like
The installation wants me to specify the domain name. I don’t have one? It’s localhost. When I specify that in the setup, it says it needs to have at least one dot. Any ideas? That article didn’t really match what our setup is I guess.
You need to use a domain name like mydomain.local or something. I would talk to your network admin if you are unsure of what to do here.
While this write up below is for auto-renewing certs for local resources, it shows you how to “fake” the domain on your server to match what you tell Let’s Encrypt. You have to demonstrate to Let’s Encrypt that you own the domain by creating a TXT record and then you can use the NETDOM command to make your computer match.
1 Like
I saw an interesting video about cloud flare tunnels.
There is an option to bypass certificate error too.
1 Like
I finally got that SSL cert working and I am able to connect to Epicor BaqSvc using REST API. I get JSON back that parses “normally” in Postman. However, in Power BI I am struggling to get it to format into a table. Any pointers here?
If you have a working url that returns your BAQ data in JSON, then in Power BI, you should be able to choose Get Data → OData feed, and paste the link in there.
Then you can either just hit ‘OK’, or ‘Transform Data’. I generally transform it right off the bat, to at least change the column types to match the actual data type. (e.g. integer, date, etc.)
From there, you should see your query and all of the fields in the ‘Data’ pane on the right side of the screen. You can either start checking boxes on fields, or drag and drop onto the report canvas to build tables and visuals.
1 Like
I’m not sure why but initially, it was not displaying the preview properly… I restarted it and BAM, worked automatically. I didn’t have to do anything. This is pretty slick.