Problem with Save Data Flow in Power BI Service - Can't save dataflow - Works in Power BI Desktop

I have 3 servers (On Premise SqlServer), and about 300 separate databases on each server with one tenant in each database on all servers. All databases have the same schema.

In Power BI Desktop I have a report which works perfect. In Power Query I have some M-scripts which loops through the tables in all databases on all servers and append the data in each table into one “big” table for each entity for all the tenants.

This works perfect in Power BI Desktop! But when I try to do the same thing in a Data Flow in Power BI service it fails with the error message: “Can’t save dataflow. One or more entities reference a dynamic data store. BUT it also works in preview mode in the data flow.

Here is how it works:

I have a query with all the tenants and which server and database the tenant “lives” on:

I have a query for each table I want to get data from with some transformations. Exemple is Projects:

Let

    Source = Sql.Database(pServerTR, pDatabaseTR),

    dbo_Core_Customers = Source{[Schema="dbo",Item="Core_Projects"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(dbo_Core_Customers,{"Id", "DepartmentId", "Name", "Status", "Deleted", "PositionType", "WorkHours", "Created", "Active", "LocationId", "CategoryId", "ProjectTypeId", "IsFullTime"})

in

    #"Removed Other Columns"

Then this Query is transformed into a function:

let

    Source = (pServerTR as text, pDatabaseTR as text) => let

        Source = Sql.Database(pServerTR, pDatabaseTR),

        dbo_Core_Customers = Source{[Schema="dbo",Item="Core_Projects"]}[Data],

        #"Removed Other Columns" = Table.SelectColumns(dbo_Core_Customers,{"Id", "DepartmentId", "Name", "Status", "Deleted", "PositionType", "WorkHours", "Created", "Active", "LocationId", "CategoryId", "ProjectTypeId", "IsFullTime"})

    in

        #"Removed Other Columns"

in

    Source

And then we reference the query (with server, database and Tenant ID) and add a column and invoke the function. Then expand the table with the rows from each tenant:

And magically we have one table with all the projects from all the tenants!

AND IT WORKS!

Then we tried to do the same in a Data Flow in Power BI service. And as I said: it worked, even in preview. But when I tried to Save I get this Error message:

This is dreadful! The last important step fails! I have tried for days to solve it!

What can I do?

Regards Aslak Jonhaugen