unsupported data source if using a custom M function

[Anonymous]

I have a customer that wants to be able to have a report based on data from multiple databases. One thing they want is to be able to add new databases into the data model on demand when they want without actually changing the data model manually. The database schema should be the same. I don't know the current number of databases.

My solution was to build a connection table from a parameter and invoke a custom function to return the data as a table.

An example I've created for testing:

let
    Source = Json.Document(connectionStr),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"server", "db"}, {"server", "db"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded Column1", "DimProduct", each fx_getTableData([db], [server], "dw", "DimProduct"))
in
    #"Invoked Custom Function"

The function:

let
    Source = (db as text, server as text, schema as text, tableName as text) => let
        Source = Sql.Database(server,db),
        Table = Source{[Schema=schema,Item=tableName]}[Data]
    in
        Table
in
    Source

To get the data in a table I just use Table.Combine

let
    Source = connectionTable[DimProduct],
    Result = Table.Combine(Source)
in
    Result

This approach works fine in Power BI Desktop. But when I publish the dataset to the services I'm not able to refresh it and get the following error:

Data source error:	Unable to refresh the model (id=2510949) because it references an unsupported data source.

Are there any other ways to have a dynamic data source and have the ability to refresh data manually or scheduled?

Are the custom functions not supported in Power BI Services?

Thanks

[GilbertQ]

Hi there

As far as I know the data sources have to be created as part of the PBIX.
I do know that whilst you can modify existing data sources with parameters, you cannot add new data sources dynamically in the Power BI Service.

This might work for you if you want to update it via the API: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources

[Anonymous]

Hello @GilbertQ 

Thanks for your answer.

Unfortunately update via API will not work for me as the datasource that I use is ODBC and it's not supported.

So, the only solution atm is to add new sources manually (if via API is not an option), correct?