cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic Function Parameters using a Json Document

Hello,

I'm trying to create some of the function parameters using a json file.

If  I set the JSON file as "Compile", I can generate the documentation value using the JSON, however,

if I'm getting the JSON from an absolute path("Content"), I can't generate the documentation values.

 

 

GetServersContent = (filename as text) as list => 
    let
        json = Json.Document((File.Contents(filename)),
        servers = json[Servers],
        table = Table.FromList(servers, Splitter.SplitByNothing()),
        expanded = Table.ExpandRecordColumn(table, "Column1", {"Name", "URL"}),
        tolist = expanded[URL]
    in tolist;

GetServersCompile = (filename as text) as list => 
    let
        json = Json.Document(Extension.Contents(filename)),
        servers = json[Servers],
        table = Table.FromList(servers, Splitter.SplitByNothing()),
        expanded = Table.ExpandRecordColumn(table, "Column1", {"Name", "URL"}),
        tolist = expanded[URL]
    in tolist;

 

 

 

 

 

{
	"Servers": [{
			"Name": "Localhost",
			"URL": "http://localhost:5980"
		},
		{
			"Name": "Localhost1",
			"URL": "http://localhost:5981"
		},
		{
			"Name": "Localhost2",
			"URL": "http://localhost:5982"
		}
	]
}

 

 

 

This is my json file, I'm just trying to get the server URL's and set Documentation.AllowedValues using them. 

 

 

 

 

shared SampleConnector.Connector = Value.ReplaceType(ConnectorImpl, Params);

Params = type function(
    ServerUrl as ( type text meta[
    Documentation.FieldCaption = "Server URL",
    Documentation.FieldDescription= "Enter the server URL to connect",
    Documentation.AllowedValues = GetServersContent("C:\powerbi.json")
//  Documentation.AllowedValues = GetServersCompile("powerbi.json")
    ])
) as table meta [
    Documentation.Name="Sample Connector",
    Documentation.LongDescription = "Connects to Sample."
];

ConnectorImp = () => ...

 

 

If I use 

    Documentation.AllowedValues = GetServersCompile("powerbi.json"), everything works properly.

But,

    Documentation.AllowedValues = GetServersContent("C:\powerbi.json") doesn't generate the allowedvalues.

11 REPLIES 11
TheInvoker
Regular Visitor

In M language (power connector), I download data and convert it to json like this

 

json = Json.Document(source)

 

I know for sure this json has 1 record like this

 

{Count: 3}

 

But If I return this, it shows 1 record like that. But as a test I changed it to return a number like 3 (hardcoded), and in power BI it showed just a value instead of a table. How can I return the value instead from the json document? Something like json[0]["Count"]?

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

As tested, when using File.Contents,  I use a file path instead of a file name.

Capture1.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

That didn't fix my problem.

 

After further research, I believe that using another Data Source before the primary custom connector causes the errors.

 

I guess my question is,

Can I access an external file before actually using the connector?

VerAS
Frequent Visitor

Hi @Anonymous,

 

Did you end up finding a solution/workaround to this problem?

 

I have been trying to populate Documentation.AllowedValues with the result of an API call using Web.Contents and am running into the same problem.

 

@v-juanli-msft, is it possible to do the above sequence?

Anonymous
Not applicable

Hi @VerAS ,

No, I have not found a solution for my problem. I figured that we are trying to access a datasource before even starting up our connector. With that being said, I'm not really sure if my assumption is correct and it would be nice to get a confirmation from one of the admins. @v-juanli-msft 

Have you tried wrapping your function call in a Diagnostics.Trace to see if there are any errors?

VerAS
Frequent Visitor

I did try Diagnostics.Trace
Unfortunately the connector fails to even start up, so it doesn't write anything.

 

If I make the connector return a function-type value, I can load it up and invoke it though Power BI desktop (with the correct AllowedValues from my web request). However I am building this as part of a product with ongoing use, and this functionality will be unacceptable from an end-user point of view.

 

@v-juanli-msft 

So using something like:

Diagnonstics.Trace(TraceLevel.Information, try GetServersContent("C:\powerbi.json"), try GetServersContent("C:\powerbi.json") otherwise null)

 Doesn't result in anything?

VerAS
Frequent Visitor

That's correct.

When executing the connector in Visual Studio, the Output window freezes and I have to force it to close.

When loading up Power BI Desktop with the connector built, the initialiser freezes and crashes without loading anything up.

VerAS
Frequent Visitor

You may run into firewall issues doing this. The privacy settings can be disabled on a per user baisis in the options menu.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors