cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krizsz Frequent Visitor
Frequent Visitor

Dynamically or manually add data sources to power bi

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamically or manually add data sources to power bi

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.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Dynamically or manually add data sources to power bi

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.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)