REST API, Postman, and Excel Power Query Editor

Kinetic ERP
1

I have a BAQ in Epicor with two parameters. I can call it from Postman and Swagger Help without a problem. I can also call it from Postman without a problem. But I cannot get it to work in Power Query Editor from Excel.

I have an API created and the BAQs are assigned to the scope. The scope is applied to the API. I also have an integration user account for just this purpose. I can’t figure out how to replicate my settings from Postman into Power Query Editor.

From Postman:
Setup with Basic Auth pointing to integration user account.
Also passing in API key in header:
x-api-key={{ApiKey}}
Those start and end dates are also parameters.
{{Host}}/api/v2/odata/VTAERO/BaqSvc/EarnedHours5/Data?StartDate=‘2025-02-03’&EndDate=‘2025-02-04’

In Excel Power Query Editor I have this code:

let
    StartDate = Date.ToText(StartDate, "yyyy-MM-dd"),
    EndDate = Date.ToText(EndDate, "yyyy-MM-dd"),
    ApiKey = apikey,
        
    // URL with API Key as query parameter
    URL = "{{MyServer}}/api/v2/odata/VTAERO/BaqSvc/EarnedHours5/Data?startDate=" & StartDate & "&endDate=" & EndDate & "&ApiKey=" & ApiKey,

    // Fetch data using Web.Contents
    Source = Json.Document(Web.Contents(URL)),

    // Assuming the returned data is a JSON array, convert it into a table
    SourceTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Changed Type" = Table.TransformColumnTypes(SourceTable, {
        {"LaborDtl_PayrollDate", type date}, 
        {"Calculated_ProdEarnedHours", type number}, 
        {"Calculated_EmpEff", type number}, 
        {"LaborDtl_EarnedHrs", type number}, 
        {"LaborDtl_LaborQty", type number}, 
        {"LaborDtl_BurdenHrs", type number}, 
        {"LaborDtl_LaborHrs", type number}
    })
in
    #"Changed Type"

I keep getting “Access to the resource is forbidden.” I have setup the power query data source as basic with the integration account credentials. I also tried it with just the API key. It didn’t like it either way. I must be missing something important.

I can get the data to work if I only use basic auth and use my own creds. But I don’t want to include my cred s in a file I share on the network. Instead, I want anyone that opens it to be able to update the file from Epicor with the built-in creds. I feel like I have done this once before, but I couldn’t find a solution that works.

Thanks for your time!
Nate

2

Try this post

2 Likes
3

I used the code in that post to modify my code to create the following.

let
    // Encode Basic Authentication manually (username:password in Base64)
    Base64Auth = "Basic " & Binary.ToText(Text.ToBinary("datapuller:mypassword"), BinaryEncoding.Base64),

    // API Key
    ApiKey = "myapikey",

    // Headers
    Headers = [
        #"Authorization" = Base64Auth,
        #"X-API-Key" = ApiKey,
        #"Content-Type" = "text/plain"
    ],

    // Query Parameter
    paramQuery = "EarnedHours5",

    // Fetch Data
    Source = OData.Feed("https://myserver/api/v2/odata/VTAERO/BaqSvc/" & paramQuery & "/Data?", Headers, [Implementation = "2.0"])
in
    Source

But I keep getting the “Please specify how to connect” error and when I click to edit my credentials it brings up the anonymous odata feed connection.

4

Only my phone will try looking once not in meetings.

1 Like
5

I’d like to figure this out as well, but… Any credentials that grant access are an entry point, basic auth or API key. Scope can optionally be different between user account and API key, but that would depend on security manager activity.

So, I’ve been setting these things up with integration user accounts, and I don’t grant them access to anything but an access scope. Anyone who tries being clever and logging in with stored credentials won’t get anywhere, but I don’t want employees to potentially waste time finding that out so I paste in the hashed authorization string (looks like, basic <hashed username and pw>). For the record - a foolproof way to get that (or check your work) is, log in to your default Swagger instance and snoop through the requests in your browser’s developer tools.

That scopes the basic auth to the same access as the access scope’s API key. A security manager could grant the user account broad access that isn’t accessible to an API key. Not perfect. Could be worse; if a security manager is doing this, you have bigger problems anyway.

Just to pile on, here’s a copy of my own M language starter that I keep up my sleeve.

_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>"
            ]
        ]
    )
)

PS - keep an eye on that account’s locked out status, the error is the same for bad auth as it is for locked out!

1 Like
6

Excel always knows better how OData URL should look like so it rewrites it all the time. You are on prem so you can look in IIS log to find correspondant http call and see how it was changed.

7

Can Excel do V2 connections? I thought there was an issue with them?

8

V2 is no problem. V1 needs a little massaging since the methods are practically all POST. Power query / Power BI will POST from M but there isn’t an easy button for it that I’ve seen. I haven’t looked very hard though, I write most of my M in the editor.

I leave V1 alone unless there’s no other option. What could go wrong? Imagine intending to read with PUSH syntax and typo’ing in a write endpoint by mistake! Unlikely but not impossible. Completely not an issue in V2 since read methods are GET and the M syntax is explicitly different for GET and POST.

9

Try this:

image
image1025×422 21.2 KB

// p function
let
    Source = (paramName) => (Excel.CurrentWorkbook(){[Name=paramName]}[Content]{0}[Column1])
in
    Source

// ODataCall Info
let
    // Encode Basic Authentication manually (username:password in Base64) : Change appropriately
    Base64Auth = "Basic " & Binary.ToText(Text.ToBinary("datapuller:mypassword"), BinaryEncoding.Base64),

    // API Key : Change appropriately
    ApiKey = "myapikey",

    // Headers
    Headers = [
        #"Authorization" = Base64Auth,
        #"X-API-Key" = ApiKey,
        #"Content-Type" = "text/plain"
    ],

    // Query Name
    paramQuery = "EarnedHours5",

    // Query Parameter(s)
    startDate = if (p("WeekStartDate") = null) then "" else "&startDate="&Date.ToText(DateTime.Date(p("WeekStartDate")),"yyyy-MM-dd") ,
    endDate = if (p("WeekEndDate") = null) then "" else "&endDate="&Date.ToText(DateTime.Date(p("WeekEndDate")),"yyyy-MM-dd") ,

    // Fetch Data with params
    Source = OData.Feed("https://myserver/api/v2/odata/VTAERO/BaqSvc/" & paramQuery & "/Data?" & startDate & endDate, Headers, [Implementation = "2.0"])
in
    Source

EDIT: Forgot to say you should have 2 named cells, WeekStartDate and WeekEndDate where you enter parameters. The p() function will grab them from your worksheet.

2 Likes
10

Global permissions: {Anonymous}
https ://server/Instance/api/v2/odata
image

This should be sufficient to get it working.

2 Likes
11

Thank you for getting me there! This is what I ended up with:

  1. I realized I didn’t need parameters, and just ran the BAQ for a year range.
  2. My M code:
let
    Base64Auth = "Basic " & Binary.ToText(Text.ToBinary("datapuller:MYACTUALPASSWORD"), BinaryEncoding.Base64),
    ApiKey = "MYACTUALAPIKEY",

    Headers = [
        #"Authorization" = Base64Auth,
        #"X-API-Key" = ApiKey,
        #"Content-Type" = "text/plain"
    ],

    paramQuery = "EarnedHours5ThisYear",

    Source = OData.Feed("https://MYACTUALSERVER/api/v2/odata/VTAERO/BaqSvc/" & paramQuery & "/Data?", Headers, [Implementation = "2.0"])
in
    Source
  1. The data source had to be OData feed, anonymous.
  2. I think that pointing the permissions to https ://server/Instance/api/v2/odata was also important.

Initially this didn’t work, but I “Close & Loaded”, and it seems to have connected properly! Awesome!

I also choose to refresh on open, so now as long as the content is enabled, any user should be able to see up to date information when opening the excel file. Thank you so much!

OK. Yeah, I am hard coding both an API key, and user creds into my M code, but I am not a hacker and I don’t know how to do it better! No one here will ever dive deep enough to find those creds, and if they did, I don’t think they could use them for anything nefarious. My user creds are an integration account, so I don’t feel much is at risk. The API key also has an access scope limiting its reach to only a couple of BAQs.

I am happy to leave this solution right here, but I am curious, what could I do better?

2 Likes
12

If @Mark_Wonsil had any hair left, it would be standing on end. That’s not a very zero trust mindset Nate! :laughing:

3 Likes
13

I’m going to bite on this because I am confused. I know M code as being for CNC machines. Is this a different or the same code?

14

Yes, they are both called M. M language for power query is also called Power Query Formula Language. While M code for machines is more like instructions.

2 Likes
15

I was working on a UserForm version to alleviate that type of security issue only requires VBA macros to be enabled to work properly, just get overloaded sometimes and hard to reach back to less time sensitive items. Too often others’ poor planning becomes my emergency :frowning:

You can also start with simple blank query {or even Other Sources > Web} and you will get the appropriate popups for OData feed security connection type unless you have set them already. You can always adjust using the Data Source settings in the Power Query Editor ribbon. Just select the Global permissions radio button.

Anyways, congrats on getting this working! Glad to assist.

1 Like