Unleashed inventory system and Power Query

[Anonymous]

Good afternoon,

I am trying to write queries to our firm's instance of Unleashed, an inventory management software (https://www.unleashedsoftware.com/).

I have searched the web and forum for instances of this being done before, but cannot find them, and I am new to PowerBI (and Power M-Query). A little guidance would be appreciated. 

First, some fundamentals. I have our Unleashed API key and our subscription allows calls. We have data in the system. They have an API Sandbox, where you can test out get/post queries before implementing them. I have checked that this is working:

However, the documentation does not explain how to retrieve this in the power query language.

I want to retrieve a stock-on-hand list, as a starting point. Here is the page on Unleashed. 

I have an ID and a Key for this API, so I tried entering them in Power Query like this, following advice from this post on the forum to get around the 'web needs api key id specified':

However, I still get the same error.

I have previously managed to advance past this stage, but then had error-ridden queries. 

I would be grateful for any help.

Side note: I am doing this for a number of applications which do not have documentation written in M-Query. Is there some standard methodology I can follow for trying to connect an API to PowerBI? 

[suebayes]

Just been working on this and you have to take the API key together with the url parameters, eg, completed for sales orders and pass both into SHA256 encryption and then into binary, that then will enable you to authenticate.  Sha256 encryption can't be done inside power query with neither R nor Python supporting the particular modules required.

Once you get the data in then you have to convert the dates from epox to power query dates.... not easily accessible

    private static string GetSignature(string args, string privatekey) 
    { 
        var encoding = new System.Text.UTF8Encoding(); 
        byte[] key = encoding.GetBytes(privatekey); 
        var myhmacsha256 = new HMACSHA256(key); 
        byte[] hashValue = myhmacsha256.ComputeHash(encoding.GetBytes(args)); 
        string hmac64 = Convert.ToBase64String(hashValue); 
        myhmacsha256.Clear(); 
        return hmac64; 

[Anonymous]

Thanks, that's a brilliant response. Getting some of the basics explained is hugely helpful. 

I've given it a go to write my own queries, and I was hoping you'd take a look and maybe help me troubleshoot. This first one is based on a REST API query that I have managed to make work.

However, I get the error that it's still just getting HTML - which I think means it's not actually interfacing with the API and taking data, it's just copying the webpage. 

Here is my second attempt, based on a collection of forum threads. This one seems to have a little more success.

Then it asks me to 'specify how to connect', so I switch back to Web-API authentication and paste my key. After that, I seem to get errors with the json instead:

I'm not entirely sure how to proceed from here.

Advice once again really appreciated. Also, please could you link to Chris' blog? I searched 'Chris Webb REST API Power M query' and I found his blog, but I'm not sure the articles under the Power BI API section I was looking at had details on how to build basic M query connections. 

[otravers]

This entry (and the others it links to) is a good place to start on Chris's blog:

https://blog.crossjoin.co.uk/2021/01/10/handling-multiple-url-query-parameters-with-the-same-name-us...

Whenever you run into an error, you want to try and isolate it. The idea is to solve the smallest, most simple problems. In your second example, I'd remove the FormatAsJsonQuery step to see what the return of Web.Contents look like. I often start development with a query so that I can see the result of each step, and it's only when it's working that I turn it into a reusable function with parameters.

[Anonymous]

I've tried to isolate this error by taking out that Json bit, and breaking my code into smaller blocks where I ideally understand what's going on. The web.contents documentation has helped advance my understanding. 

However, I seem to be pulling the webpage as HTML, not actually taking data out of the API, and I don't really understand why/how to change this. Results:

From my simplified query:

I think this is just because I have written the query in Web.Contents wrong. When I actually save the code, it keeps making some auto changes based on the fact it recognises the incoming data as HTML. 

[otravers]

You're getting HTML in return because you're calling the root of the api subdomain. You need to call an actual endpoint, for instance https://api.unleashedsoftware.com/ProductPrices for:

https://apidocs.unleashedsoftware.com/ProductPrices

Read the Getting Started section of https://apidocs.unleashedsoftware.com/

[otravers · Accepted solution]

You'll have to write your own headers in your Power Query function to send the api-auth-id, api-auth-signature, and other headers requested by the Unleashed API. Authenticate as "anonymous" in Power BI and handle all the authentication requirements in your M code. You're in luck that Unleash is simple as they don't require Oauth tokens.

There's no "standard methology" because every API is different. It's very rare that you'll find M documentation from an API provider, usually you'll have cURL, JavaScript, Python, PHP... the usual suspects. Best place to start is from the vendor sandbox as you did, or use Postman if there's no sandbox.

Chris Webb has good content on his blog about making API calls from Power BI. It's not for the faint of heart, but it can be done. Not the easiest thing to do if you've never used Power Query before as you're likely to go from on roadblock to the next, that said it would be a great learning experience if you have the time and inclination.