Epicor BAQs to Power BI/Excel

Epicor ERP 10
1

So I’m trying to pull some BAQ’s into Power BI/Excel.
I was successfully able to create a new API for that, went to the REST API Epicor Services and tested the BAQ and everything seems to work perfectly as I tested, and data is coming as expected.

But when trying to pull it to PBI I’m having the next error and I have no idea how to make it work, I already spent my last 3 braincells and can’t figure it out how to make it work, not sure what I’m missing.

image
image462×186 6.04 KB

Worth to mention that we are Cloud and login into Epicor using Identity Provider.
the Curl is returning data and no errors over the resthelp, and api key was added to the headers.

In PBI, tested using anonymous access, web API, and basic and none worked.

Thanks

1 Like
2

The incantation is just a little particular.

Here’s the snippet I’ve been kicking around for ages. The parameters aren’t necessary. Snip those bits out if you don’t need them, it’s just an example of “how to parameter”.

The “auth string” is a hashed copy of the account’s credentials. I copy them from the network activity in developer tools while doing anything in swagger while logged in from the account I’d be using in PBI. Looks a little less sketchy than plain text, not actually more secure, just useful for deflecting peanut gallery comments if non techy people see it.

_fromDate = Date.ToText(Date.AddDays(Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -2)), 1), [Format="MM-dd-yyyy"])
, _throughDate = Date.ToText(Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -1)), [Format="MM-dd-yyyy"])
, Source = Json.Document(
    Web.Contents(
        "https://centralusdtapp<server id>.epicorsaas.com/SaaS<instance id>/api/v2/odata/<company number>/BaqSvc"
        ,[
            RelativePath="<baq name>/Data"
            ,Query=[
                FromDate=_fromDate
                ,ThroughDate=_throughDate
            ]
            ,Headers=[
                Authorization="<auth string>"
                , #"X-API-Key"="<key>"
            ]
        ]
    )
)

…And then once you get it working and PBI sees you relax, it’ll decide it doesn’t like your connection settings, but that’s only annoying.

4 Likes