Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Adam15
Frequent Visitor

Column as Database name

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"

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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]"

  

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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]"

  

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors