topic Re: Github PowerBi connector - JSON error in Developer

Github PowerBi connector - JSON error

ferryv — Wed, 11 Sep 2024 14:32:50 GMT

When connecting to the raw data in github via the Github Connector (I have e.g. a function in the repo that I want to use to process information, like e.g. expnding all columns), I get a JSON error.

It looks like the function is attempting to output a JSON document, but since the rawdata itself might have a comment in it, like:

/*
Some comments
*/

(previousStep as table)=> 
...

The JSON document returned comes up with an erorr that there are unknown characters (likely due to the comments as it references a "/" on position 0.

While I can get the data via Web.Contents fine (don't get errors in Desktop and using Expression.Evaluate allows me to use the function), the issue I am having is that while I can connect via OAuth to the repo via the Github connector, the Web.Contents authentication does not appear to allow OAuth connection, but requires e.g. a PAT.

A subsequent issue via Web.Contents is that any subsequent API call (e.g. to JIRA) will not refresh once published as an error comes up stating:

[Unable to combine data] .... references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. Which I am trying to resolve and I want to check if the Github connector resolves this..

Can someone advise if it is possible to somehow strip or ignore the comments then invoking the step?

For example, the Github steps would be:

let 
Connect = Github.Tables("<owner>", "<repo>"){[Key="contents"]}[Data], 
Source =Connect( "https://raw.githubusercontent.com/<owner>/<repo>/main/<filename>.pq" )
in Source

where <filename> is the file containing the function with the comments.

When using Web.Contents, similar output would be achieved via

Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/<owner>/<repo>/main/<filename>.pq"))

Re: Github PowerBi connector - JSON error

Anonymous — Thu, 12 Sep 2024 08:42:10 GMT

Hi, @ferryv

Regarding the issue you raised, my solution is as follows:

1.Firstly, regarding your GitHub connector, it is still in the development stage, so there are some issues at present. You may encounter warnings when using the GitHub connection:

2.Secondly, concerning the error you mentioned, “references other queries or steps, so it may not directly access a data source,” you can try merging multiple queries into one. Here is a screenshot of the relevant response:

Solved: Query references other queries or steps, so it may... - Page 2 - Microsoft Fabric Community

You can also try ignoring the privacy levels:

Chris Webb's BI Blog: formula firewall (crossjoin.co.uk)

https://learn.microsoft.com/en-us/power-bi/enterprise/desktop-privacy-levels

3.Lastly, regarding the issue of “removing or ignoring comments and then invoking the step,” you can try creating a notebook in Lakehouse and then use the json.loads() function in Python to parse the JSON data

https://learn.microsoft.com/en-us/power-bi/guidance/powerbi-implementation-planning-data-gateways#personal-gateway

Of course, if you have any new ideas, you are welcome to contact us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Github PowerBi connector - JSON error

ferryv — Thu, 12 Sep 2024 08:49:03 GMT

Thanks. I also found that it is not just comments causing the JSON error. When removing them from github, I still get the error but with the open bracket "(". I am just continue to try to make it work with Web.Contents and if I can't get that to work I will likely end up copying the function in directly (which does work, but means the management in github is no longer viable)

Re: Github PowerBi connector - JSON error

Anonymous — Sun, 15 Sep 2024 08:15:51 GMT

Hi, @ferryv

Thank you for your prompt response. Your suggested approach is indeed a good alternative.

However, based on the new information you provided, we suspect there might be an issue with the processing in Power Query. Here are two recommendations:

1.First, we suggest using the Json.Document() function to convert the binary output of the Web.Contents() function to JSON. This aligns more closely with the official examples, as we cannot rule out errors caused by Text.FromBinary(). For details, please refer to the following documentation:

Web.Contents - PowerQuery M | Microsoft Learn

Json.Document - PowerQuery M | Microsoft Learn

2.Second, you can use the Try syntax to debug each step of your Power Query process:

For details, please refer to the following documentation:

https://learn.microsoft.com/en-us/power-query/error-handling#use-try-with-custom-logic

I hope my answer is helpful to you.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Github PowerBi connector - JSON error

ferryv — Tue, 24 Sep 2024 15:19:36 GMT

I have resolved the issue writing some power query myself and found a workaround for the dynamic data source issues I had.

For those that are interested, I documented the approach here: https://www.linkedin.com/pulse/using-power-queries-directly-from-github-repository-van-der-vorst-wm6pe/