Custom Data Connector UI - Dropdown list

[cdineshrajabe]

I am trying to create a custom data connector for Power BI and  I want to display a dropdown list in the UI, So I am using the file.Contents (local path) function to get the list of values. While using file.Contents(local path) function in .pq file , I am getting the following error..

[Anonymous]

Have we found any solutions for this problems? Thanks

[Anonymous]

@cdineshrajabe,

Could you please post detailed Power query code here?

Regards,
Lydia

[cdineshrajabe]

Hi @Anonymous,

Thanks for the response. Please find the following code,

section HelloWorld;

[DataSource.Kind="HelloWorld", Publish="HelloWorld.Publish"]
shared HelloWorld.Contents = Value.ReplaceType(HelloWorldImpl, HelloWorldType);

HelloWorldType = type function (
    message as (type text meta [
        Documentation.FieldCaption = "Message",
        Documentation.FieldDescription = "Text to display",
        Documentation.SampleValues = {"Hello world", "Hola mundo"}
    ]),
    name as (type text meta [
        Documentation.FieldCaption = "Names",
        Documentation.AllowedValues = ListOfNames //{ "1", "2", "3" }
    ]))
   as table meta [
        Documentation.Name = "Hello - Name",
        Documentation.LongDescription = "Hello - Long Description",
        Documentation.Examples = {[
            Description = "Another example, new message, new count!",
            Code = "HelloWorldWithDocs.Contents(""Goodbye"", 1)",
            Result = "#table({""Column1""}, {{""Goodbye""}})"
        ]}
    ];

HelloWorldImpl = (message as text, name as text) as table =>
    let
       listOfMessages = List.Repeat({message & name},5),
       table = Table.FromList(listOfMessages, Splitter.SplitByNothing())
    in
        table;

 Names =
        let
            Source = Csv.Document(File.Contents("F:\temp\ListofNames.txt"),[Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
            GetTable = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
            FilterTable = Table.SelectRows(GetTable, each Text.StartsWith([Column1], "[") and Text.EndsWith([Column1], "]"))
        in
            FilterTable;
               
ListOfNames = Table.ToList(Names);

HelloWorld = [
    Authentication = [
        Anonymous = []
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

HelloWorld.Publish = [
    Beta = true,
    ButtonText = { Extension.LoadString("FormulaTitle"), Extension.LoadString("FormulaHelp") },
    SourceImage = HelloWorld.Icons,
    SourceTypeImage = HelloWorld.Icons
];

HelloWorld.Icons = [
    Icon16 = { Extension.Contents("HelloWorld16.png"), Extension.Contents("HelloWorld20.png"), Extension.Contents("HelloWorld24.png"), Extension.Contents("HelloWorld32.png") },
    Icon32 = { Extension.Contents("HelloWorld32.png"), Extension.Contents("HelloWorld40.png"), Extension.Contents("HelloWorld48.png"), Extension.Contents("HelloWorld64.png") }
];

[cdineshrajabe]

I am creating a custom data connector for PowerBI and want to display a drop-down list in the UI, So I am using file.Contents(localpath) function to get the list of values. While using file.Contents(local path) function in .pq file , I am getting the following error.. 

  code : 

section HelloWorld;

[DataSource.Kind="HelloWorld", Publish="HelloWorld.Publish"]
shared HelloWorld.Contents = Value.ReplaceType(HelloWorldImpl, HelloWorldType);

HelloWorldType = type function (
    message as (type text meta [
        Documentation.FieldCaption = "Message",
        Documentation.FieldDescription = "Text to display",
        Documentation.SampleValues = {"Hello world", "Hola mundo"}
    ]),
    name as (type text meta [
        Documentation.FieldCaption = "Names",
        Documentation.AllowedValues = ListOfNames //{ "1", "2", "3" }
    ]))
   as table meta [
        Documentation.Name = "Hello - Name",
        Documentation.LongDescription = "Hello - Long Description",
        Documentation.Examples = {[
            Description = "Another example, new message, new count!",
            Code = "HelloWorldWithDocs.Contents(""Goodbye"", 1)",
            Result = "#table({""Column1""}, {{""Goodbye""}})"
        ]}
    ];

HelloWorldImpl = (message as text, name as text) as table =>
    let
       listOfMessages = List.Repeat({message & name},5),
       table = Table.FromList(listOfMessages, Splitter.SplitByNothing())
    in
        table;

 Names =
        let
            Source = Csv.Document(File.Contents("F:\temp\ListofNames.txt"),[Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
            GetTable = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
            FilterTable = Table.SelectRows(GetTable, each Text.StartsWith([Column1], "[") and Text.EndsWith([Column1], "]"))
        in
            FilterTable;
               
ListOfNames = Table.ToList(Names);

HelloWorld = [
    Authentication = [
        Anonymous = []
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

HelloWorld.Publish = [
    Beta = true,
    ButtonText = { Extension.LoadString("FormulaTitle"), Extension.LoadString("FormulaHelp") },
    SourceImage = HelloWorld.Icons,
    SourceTypeImage = HelloWorld.Icons
];

HelloWorld.Icons = [
    Icon16 = { Extension.Contents("HelloWorld16.png"), Extension.Contents("HelloWorld20.png"), Extension.Contents("HelloWorld24.png"), Extension.Contents("HelloWorld32.png") },
    Icon32 = { Extension.Contents("HelloWorld32.png"), Extension.Contents("HelloWorld40.png"), Extension.Contents("HelloWorld48.png"), Extension.Contents("HelloWorld64.png") }
];

Note: The ListofNames, I am getting the values from .txt file. If I hardcode the values {"1","2","3"} it works fine.

Documentation.AllowedValues = ListOfNames //{ "1", "2", "3" }

[PowerBISDK]

Hi,

Did you manage to resolve this? I have the same question and posted my query below:

https://community.powerbi.com/t5/Developer/Custom-data-connector-SDK-help-required/m-p/547696#M17057

Please let me know if You have tried something and it worked.

Kind regards,

[cdineshrajabe]

Hi Lydia,

Thanks for the response. Please find the source code below, 

Note:  ListOfNames //{ "1", "2", "3" } if the ListOfNames is replaced with static values{ "1", "2", "3" }, I am able to get the values. 

section HelloWorld;

[DataSource.Kind="HelloWorld", Publish="HelloWorld.Publish"]
shared HelloWorld.Contents = Value.ReplaceType(HelloWorldImpl, HelloWorldType);

HelloWorldType = type function (
message as (type text meta [
Documentation.FieldCaption = "Message",
Documentation.FieldDescription = "Text to display",
Documentation.SampleValues = {"Hello world", "Hola mundo"}
]),
name as (type text meta [
Documentation.FieldCaption = "Names",
Documentation.AllowedValues = ListOfNames //{ "1", "2", "3" }
]))
as table meta [
Documentation.Name = "Hello - Name",
Documentation.LongDescription = "Hello - Long Description",
Documentation.Examples = {[
Description = "Another example, new message, new count!",
Code = "HelloWorldWithDocs.Contents(""Goodbye"", 1)",
Result = "#table({""Column1""}, {{""Goodbye""}})"
]}
];

HelloWorldImpl = (message as text, name as text) as table =>
let
listOfMessages = List.Repeat({message & name},5),
table = Table.FromList(listOfMessages, Splitter.SplitByNothing())
in
table;

Names =
let
Source = Csv.Document(File.Contents("F:\temp\ListofNames.txt"),[Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
GetTable = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
FilterTable = Table.SelectRows(GetTable, each Text.StartsWith([Column1], "[") and Text.EndsWith([Column1], "]"))
in
FilterTable;

ListOfNames = Table.ToList(Names);

HelloWorld = [
Authentication = [
Anonymous = []
],
Label = Extension.LoadString("DataSourceLabel")
];

HelloWorld.Publish = [
Beta = true,
ButtonText = { Extension.LoadString("FormulaTitle"), Extension.LoadString("FormulaHelp") },
SourceImage = HelloWorld.Icons,
SourceTypeImage = HelloWorld.Icons
];

HelloWorld.Icons = [
Icon16 = { Extension.Contents("HelloWorld16.png"), Extension.Contents("HelloWorld20.png"), Extension.Contents("HelloWorld24.png"), Extension.Contents("HelloWorld32.png") },
Icon32 = { Extension.Contents("HelloWorld32.png"), Extension.Contents("HelloWorld40.png"), Extension.Contents("HelloWorld48.png"), Extension.Contents("HelloWorld64.png") }
];

Do we need any authentication for accessing the local file?

Source = Csv.Document(File.Contents("F:\temp\ListofNames.txt"),[Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),