Dynamically or manually add data sources to power bi

[Krizsz]

Hi, 

I want to create a function that fetches content of a table from a server and a database.

But i want this to be dynamic. So i can pass along a list of servers where i fetch contents from the same table name.

This works when in power query se example below. 

Function:

let getserver = (servername) =>
let
    Source = Sql.Databases(servername),
    point_to_my_database = Source{[Name="my_database"]}[Data],
    point_to_my_table = point_to_my_database{[Schema="dbo",Item="my_table"]}[Data]
in
    point_to_my_table
in
    getserver

and query that extracts data from tables:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstclSK1YGxnZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [serverlist = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"serverlist", type text}})
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnGetServer([serverlist])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"DatabasID"}, {"DatabasID"})
in
    #"Expanded Custom"

when i run this query i get results from all (both) of my servers added to the list:

But when i want to publish and schedule this report none of the servers in my list are available as data sources.

So is there a way to either add them as data sources manually? or by using my list?

[dax · Accepted solution]

Hi Krizsz,

As I know, Power BI currently does not support parameterized data source definitions, also known as dynamic data sources. For example, you can't parameterize the data access function Sql.Database("SqlServer01", "AdventureWorks"). If your dataset relies on dynamic data sources, Power BI informs you that it detected unknown or unsupported data sources. You must replace the parameters in your data access functions with static values if you want Power BI to be able to identify and connect to the data sources. For more information, see Troubleshooting unsupported data source for refresh and refresh-data 

Best Regards,
Zoe Zhi

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