Column as Database name

[Adam15]

Hello,

On our server there are multiple Databases and I'm trying to download the same data from every database. The problem is our admin sometimes creates a new one and I need the data from there too, but he doesn't tell me when that happens so I can't add it manually. I have already made a query which first downloads all the database name and then using this information downloads the data I need. The problem is I can't refresh it on the Power BI app. Could you help me? My code:

let
    Source = Sql.Databases("MyServerName"),
    #"Removed Columns" = Table.RemoveColumns(#"Source",{"Data", "Kind"}),
    #"Hívott egyéni függvény" = Table.AddColumn(#"Removed Columns", "fxFelhasznaloProfil", each let
    Source = (adatbazis as text) => let 
        Source = Sql.Database("MyServerName", database, [CreateNavigationProperties=false, Query="select * from UserData"]),
    #"Added Custom" = Table.AddColumn(Source, "Database", each database)
in
    #"Added Custom"
in
    Source([Name])),
    #"Expanded fxFelhasznaloProfil" = Table.ExpandTableColumn(#"Hívott egyéni függvény", "fxFelhasznaloProfil", {"Nev", "LicencProfil", "Utolsó belépési dátum", "Rendszerfelügyelő", "Hasznalhato", "Menüsor név"}, {"Nev", "Profil", "Utolsó belépési dátum", "Rendszerfelügyelő", "Hasznalhato", "Menüsor név"})
in
    #"dxFelhasznaloProfil"

[lbendlin · Accepted solution]

You must use SQL.Database() , not SQL.Databases()

Specify a sample database in the connection string (so that the Power BI service is fooled into thinking this is a constant source), and then you change the query to override the database name .

Query="select * from [database].[dbo].[UserData]"