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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors