Search code examples
powerbipowerquerypowerbi-datasource

Change SQL Server database of a web published PowerBI report runtime

I've a PowerBI report with a SQL Server datasource thats retrieves data from a database database_{number}.

{number} is a query params of PowerBI. I can change it from Data Transform -> Edit parameters and it works correctly.

It's possibile to edit this parameter of a web published report? for example changing an URL query params?

Solution

  • 100% this can be done. First, create a connection to any one of the databases normally. Go to advanced editor, and you will see something like this:

    let
    Source = Sql.Database("1.1.1.1\address", "database_001")
in
    Source

    Simply modify this to reference the parameter:

    let
    Source = Sql.Database("1.1.1.1\address", "database_" & number) 
in
    Source

    You may want to consider changing the parameter name because number is a predefined data type, that could be ambiguous in some cases.

    One note, depending on your server capabilities, changing the reference to be dynamically generated may prevent query-folding. Query-folding is the ability for a single query to be executed against the SQL server based on your PowerQuery script. Without query-folding, a larger query will likely be executed against the server, and your data model will take longer/more resources to refresh. https://learn.microsoft.com/en-us/power-query/power-query-folding