Dynamic data source error in PBI Service

[Anonymous]

I'm working with multiple servers as data sources and have the query bellow which select only servers that are working (if Source... = 1). Data sources are given by parameters (ARA, GOI, IBI).

let
    fxConnection =
        (server as text) as table =>
            Table.AddColumn(
                Sql.Database(server, "Database"),
                "Server",
                each server
            ),
    #"CombinedTables" =
        Table.Combine(
            {
                if ACT_SourceARA = 1 then
                    fxConnection(ARA)
                else
                    null,
                if ACT_SourceGOI = 1 then
                    fxConnection(GOI)
                else
                    null,
                if ACT_SourceIBI = 1 then
                    fxConnection(IBI)
                else
                    null
            }
        )
in
    #"CombinedTables"

The code works perfectly on PBI Desktop, but I get the error bellow when publish on PBI Service:

"This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources."

I've read about RelativePath, but didn't find anything to workaround this error.

@Vijay_A_Verma you've helped a lot so far. May I bother you again?

Thank you!

[v-eqin-msft]

Hi @Anonymous ,

Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. Thee only way to work around this is to use use the RelativePath and Query options with the Web. Contents M function. Queries that reference Power Query parameters can also be refreshed.

Refer to:

Power Query - Dynamic Data Source and Web.Contents() - Hat Full of Data

Setting a scheduled refresh on a Dynamic Data Source in Power BI

Web.Contents(), M Functions And Dataset Refresh Errors In Power BI 

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

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