Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BH22One
Helper I
Helper I

JSON API Call Script Help

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"

 

BH22One_0-1655406669590.png

 

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:

BH22One_1-1655408577256.png

 

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!

 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hi @BH22One !

Can you elaborate how your GetAccessToken() function works?

I'm strugguling with authorization..

 

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.

Anonymous
Not applicable

Sure! No worries 🙂 

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

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

@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.

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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.

@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,

BH

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.

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:

BH22One_0-1655740680746.png

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors