topic Re: JSON API Call Script Help in Developer

JSON API Call Script Help

BH22One — Thu, 16 Jun 2022 19:52:22 GMT

Good Afternoon PBI Community,

I am fairly new to JSON language as it pertains to APIs. Currently, I have an M script in Power Query that returns a binary list as seen below (FYI I have a Bearer Token but have denoted it as "GetAccessToken()" below.)

My goal is to write the script in a way that returns a single value from one of the binary records. Not sure if this can be done but thought I'd throw it out there.

let
Source =Json.Document(Web.Contents("https://US.api.blackline.com/api/queryruns", [Timeout=#duration(0, 0, 30, 0), Headers=[Authorization="Bearer "&GetAccessToken(), ContentType="application/json; charset=utf-8"]]))

in
#"Source"

In this case, the "Record" in row 1 contains the information I need. This information being the most recent report posted to the site I am getting the data from.

The information within the "Record" for Row 1 is as follows:

Additionally I'd only like to return the "id" from this binary data "417197777".

The data on the web site I'm pulling from updates every hour with a new unique "id" thus I am looking to use my json statement as a function (fx) in Power BI to return this value. I could use Power Query and convert this to a table and filter for the max date and then remove columns, however I am looking to satisfy this requirement solely through my JSON statement and convert it to a function if possible or any other method that doesn't require additional steps in Power Query Editor.

I can provide additional detail as needed. Hoping someone has some guidance relative to this requirement.

Thanks!

Re: JSON API Call Script Help

Daryl-Lynch-Bzy — Fri, 17 Jun 2022 17:53:31 GMT

Hi @BH22One - Power Query will help you open the JSON files, but first you need to convert the "List of Records" to a "Table of Records". There should be a "Convert to Table" button to use in the CustomUI. When you have the converted table, the column heading should have an "Expand to Columns" button. This is when the Records that you are seeing will show in Rows & Columns.

Re: JSON API Call Script Help

BH22One — Fri, 17 Jun 2022 18:21:24 GMT

@Daryl-Lynch-Bzy Many thanks for the response. I have tried this approach previously and it does work beautiful in the desktop application. The problem is that Power BI Service doesn't support it when I publish the report to a Workspace. The reason being I am using the output from this step as a function that I input into a different API Call as a dynamic function. Dynamic funtions editted in with power Query aren't allowed to refresh on a Power BI Service Workspace (MAJOR miss by Microsoft in my opinion).

Thus why I am trying to do the steps you described within the JSON script.

A similar situation would be if I had data in a SQL Database that I wanted to query, I could use a Where clause to single out the result I want. I'm not familar with JSON language and ideally would need that same approach if it's possible that is.

Thanks,

Re: JSON API Call Script Help

Daryl-Lynch-Bzy — Fri, 17 Jun 2022 18:43:08 GMT

Web.Contents - PowerQuery M | Microsoft Docs - If you are having trouble with the Dynamic functions, it is likely that you have not used the "RelativePath" and "Query" features in Web.Contents. This is important because the "URL" should be the "Base" URL in you case "https://US.api.blackline.com". Additionally, this must be defined once and referenced once. I would include in the URL in Parameter. The parameter should be included in the Web.Contents directly. For example, when I create a function with two variables (URL and RelativePath) and then provide a column reference for these variables, I will run into the dynamic function error. If I drop the URL variable, and just replace with the parameter reference, the dynamic function error goes away. Power BI is now knows there can only be one URL for every call; while passing URL and Relative Path as variables to a function will confuse Power BI into thinking that the URL might be different.

Re: JSON API Call Script Help

BH22One — Mon, 20 Jun 2022 16:02:37 GMT

Hello @Daryl-Lynch-Bzy thanks for the response. I slightly follow what your saying and have read/researched how "Relative Path" could be a used in my situation, however I am struggling to understand the syntax needed.

I have the following statement...

let
Source =
Csv.Document(
Web.Contents(
"https://US.api.blackline.com/api/completedqueryrun/",[RelativePath = GetReportId() &"/CSV"], [Timeout=#duration(0, 0, 30, 0), Headers=[Authorization="Bearer "&GetAccessToken(), ContentType="application/octet-stream"]]
),[Delimiter=",", Columns=13, Encoding=65001, QuoteStyle=QuoteStyle.None]
)

When I run this I get the following error:

Is there something in my statement that's causing this?

Re: JSON API Call Script Help

Daryl-Lynch-Bzy — Mon, 20 Jun 2022 17:00:22 GMT

Hi @BH22One , does the following work:

let Source = Csv.Document( Web.Contents( "https://US.api.blackline.com/api/completedqueryrun/", [ RelativePath = GetReportId() &"/CSV", Timeout=#duration(0, 0, 30, 0), Headers=[Authorization="Bearer "& GetAccessToken(), #"Content-Type"="application/octet-stream"] ] ), [ Delimiter=",", Columns=13, Encoding=65001, QuoteStyle=QuoteStyle.None ] )

Re: JSON API Call Script Help

BH22One — Mon, 20 Jun 2022 20:23:51 GMT

@Daryl-Lynch-Bzy this worked beautifully. I was able to refresh, publish and set the refresh schedule for my report based on the requirements I had from the business.

Thanks so much for your help.

Re: JSON API Call Script Help

Anonymous — Wed, 04 Jan 2023 11:44:33 GMT

Hi @BH22One !

Can you elaborate how your GetAccessToken() function works?

I'm strugguling with authorization..

Re: JSON API Call Script Help

BH22One — Thu, 02 Feb 2023 19:31:19 GMT

Hello @Anonymous Sorry for the delay. I have been away on parental leave and logged off completely from anything work related. Are you still in need of some information on the GetAccessToken() function? You can send me a direct message and I can try and provide some assistance.

Re: JSON API Call Script Help

Anonymous — Wed, 08 Feb 2023 15:06:00 GMT

Sure! No worries 🙂

Message should be in your mailbox. Will appreciate your guidance!