Pulling BAQ into Excel - Web API, OData, JSON?

Kinetic ERP
58

Oh I wish I’d seen this sooner! This is what I get for being busy all day. I LOVE power query and M language, it’s as powerful as it is underutilized. Here’s my contribution to the snippet pile:

Some time ago I got tired of trying to visually parse oneliner Web.Contents URL’s, plus concatenating a oneliner URL often throws M and Power [Query,BI] security scope errors, not to mention the special hell that is trying to do this through the gui. So I hashed it out a reliable method from Microsoft’s documentation, stashed it away in an org file, and ever since just paste this into the advanced editor of a blank query and touch it up to fit my nefarious purposes. With bonus date params because 3/4 of my M contraptions are looking into a daterange.

_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 stuff goes here>
	]
    ]
    )
)

BTW sharp eyes might notice the Content parameter in the MS docs. Yes, that’ll do what you think it can do. Ain’t straightforward but not impossible. Reason #3984623956598723649327 to extremely carefully consider giving write privs to any userid with an assigned API key.

3 Likes