Forum Discussion
JSON API Call Script Help
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 ] )
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.
Anonymous 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 Anonymous 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?
