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

[Olga (Olga Klimova)]

Technically it should be possible to write VBA macro too.

[NateS (Nate Schoonover)]

My power query editor looks like this:
Source:

= Json.Document(Web.Contents("https://centralusdtapp01.epicorsaas.com/saas512/api/v2/odata/VTAERO/BaqSvc/OJEXForDash/Data?api-key=blahblahmyAPIblahblah"))

Navigation

let
    Source = Json.Document(Web.Contents("https://centralusdtapp01.epicorsaas.com/saas512/api/v2/odata/VTAERO/BaqSvc/OJEXForDash/Data?api-key=blahblahmyAPIkeyblahblah")),
    value = Source[value],
    value1 = value{0}
in
    value1

When I run this, I get only one record of data back. Do I need to include something else to show all the rows of data?
Here I show only the one row of data returned. If I change the value[0] to another index, then it returns that record instead.

If I don’t change anything in the power query editor, this is the base source result:

[Mark_Wonsil]

@Randy

We have a request here to use Excel to pull BAQ data

Are we seeing any movement out there with people doing analysis in other tools (Grow, EDA/Phocus, PowerBI, Tableau, etc.) vs Excel?

[NateS (Nate Schoonover)]

I was going to go with PowerBI. They (Microsoft) trick you into thinking its free until you try to share anything with the rest of the company. That is when they hit you with the subscriptions. Eventually, I want to pull data into Grafana, but I am still waiting on the server to get setup. I thought that Excel would be a good proving ground to test out pulling live data, testing out security, and building up some graphs. So far It is kind-of working. If I can get the bugs tamped down, then I think this could work for the short term until Grafana is ready to ingest some data.

[klincecum (Kevin Lincecum)]

@Randy

Yes, inquiring minds would like to know

This is what I did:

Accessing REST APIs with Basic Auth and API Key in Power Query

[Randy (Randy Stulce)]

@Mark_Wonsil

Are we seeing any movement out there with people doing analysis in other tools (Grow, EDA/Phocus, PowerBI, Tableau, etc.) vs Excel?

We’ll pull excel out of Accounting’s cold dead hands.

Our CFO is actually interested in Grow and we are likely migrating off EDA.

[NateS (Nate Schoonover)]

If I click the “list” in my source value field, then I can get a list of the 40 records in my BAQ. Then I can get the data for a single record by clicking on the value in the list. Then it returns just one value like this:

I want to have all my data in rows, and the columns should contain the values. Just like my BAQ returns. Why can I only show one record at a time?

[utaylor (Utah Taylor)]

I can help you with that, you need to expand the results to a table.

[utaylor (Utah Taylor)]

It’s hard to explain honestly, but in the second step I think you need to expand the value of the second row of your data, not the API response header row. And then keep expanding and keep expanding till you see a table haha

[Mark_Wonsil]

@Randy

We’ll pull excel out of Accounting’s cold dead hands.

Yeah, it’s definitely going to take another generation who’s not willing to have their data auto-converted to dates, has stricter local data policies, has requirements with more than 1,048,576 rows, wants to process streaming data, and seeks to avoid drag and VLookUp errors to move that needle!

[NateS (Nate Schoonover)]

I dont see how to expand it to a table and keep it that way. I can only seem to show one record with data at a time.

[NateS (Nate Schoonover)]

Alright! Now we are getting somewhere! Thank you @utaylor for the offline help.
As a summary, I was stuck in power query editor with only a basic return of my BAQ data. Utah showed me these steps to get all the data to show.
First Click “List” under your first record. Then click To Table. Then click the little stupid two arrow thing at the top of a column (seriously WTH even is this?) That was the trick. Everything else was just niceties for display.
As Utah warned me, the refresh of this data may be an issue. I will test that with some other users later.

This post from Mark is also an important part of this solution:
Pulling BAQ into Excel - Web API, OData, JSON? - Kinetic 202X - Epicor User Help Forum (epiusers.help)

[Mark_Wonsil]

@Olga

should be possible to write VBA macro too.

It my bountiful spare time, I’d like to try this with Office Scripts (JavaScript/TypeScript)). Office Script will run in Mac and on the Web/Phones, can be centralized and run on many workbooks, and may be launched with PowerAutomate.

[klincecum (Kevin Lincecum)]

@NateS

seriously WTH even is this?

It’s a damn mess is what it is. The whole shebang.

[gpayne (Greg Payne)]

@Mark_Wonsil @Olga is correct about vba. I had a v1 vba routine and since @NateS started this I changed it from a v1 to a v2 and it was pretty simple.

1. Made API-Key
2. Made Access scope and added the BAQ to it.
3. Made a BAQ user and added the Access scope to the user
4. changed v1/BaqSvc to v2/odata/CompanyID/BaqSvc
5. Added x-api-key to SetRequestHeader

[klincecum (Kevin Lincecum)]

@gpayne

I had a v1 vba routine and since @NateS started this I changed it from a v1 to a v2 and it was pretty simple.

[Mark_Wonsil]

@gpayne

@Olga is correct about

Let me fix that for you @gpayne

@Olga is correct about damn near everything.

[kananga (John K)]

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.

[klincecum (Kevin Lincecum)]

@kananga

special hell that is trying to do this through the gui

[NateS (Nate Schoonover)]

@NateS

As Utah warned me, the refresh of this data may be an issue. I will test that with some other users later.

This morning I confirmed that any user that opens this file will have to supply credentials to refresh it. The program does not save credentials. Does the VBA routine help this at all? Is there any way to avoid this? I want the data to be refreshed daily without requiring the user to enter credentials. In my mind once that API was generated, and hard-coded into the power query, that should be enough permission to pull data out.

Eventually, if/when we make a move to Grafana I am hoping that this credential issue does not persist.