Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

unsupported data source if using a custom M function

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

 

2 REPLIES 2
GilbertQ
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

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?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.