One or more tables reference a dynamic data source.

[LuiSMSR]

Hello,

I have searched on community, on google and with the help of ChatGPT and I am still not able to fix my query.

I am connecting to an Sql.Database on PowerBI Service. This particular Database is a yearly database, starting with "CTA"+YEAR.

I want to connect dinamically to the yearly CTA database, which means we are in 2023 I need to acces CTA2023, when we enter year 2024 it needs to access CTA2024 and so on. The preview shows the table with no errors.

With the code below I'm getting error "One or more tables reference a dynamic data source.". 

Any help would be much appreciated.

let
  // Step 1: Get the Current Year
  CurrentYear = Date.Year(DateTime.LocalNow()),

  // Step 2: Construct Database Name
  DatabaseName = "CTA" & Text.From(CurrentYear),

  // Step 3: Construct Connection String
  ConnectionString = "10.0.0.0", // Replace with your actual server IP
  FullConnectionString = "Provider=SQLNCLI11;Data Source=" & ConnectionString & ";Initial Catalog=" & DatabaseName & ";Integrated Security=SSPI;",

  // Step 4: Connect to the Database
  Origem = Sql.Database(ConnectionString, DatabaseName),
  Origem1 = Sql.Database(ConnectionString, DatabaseName),
  schema="dbo",
  item="movctal",
  item1="movcta",
  #"Navegação" = Origem{[Schema = schema, Item = item]}[Data],
  #"Navegação 2" = Origem1{[Schema = schema, Item = item1]}[Data],

  #"Outras colunas removidas" = Table.SelectColumns(#"Navegação", {"ano_cta", "diario", "num_lanc", "tipo_doc", "chave_doc", "debito", "credito", "ct_geral", "terceiro"}),
  #"Linhas filtradas" = Table.SelectRows(#"Outras colunas removidas", each [ct_geral] <> ""),

  #"Tipo de coluna alterada" = Table.TransformColumnTypes(#"Navegação 2", {{"data_lanc", type date}}),
  #"Outras colunas removidas1" = Table.SelectColumns(#"Tipo de coluna alterada", {"data_lanc", "mes", "descricao", "diario", "num_lanc", "ano_cta"}),

  #"Consultas intercaladas" = Table.NestedJoin(#"Linhas filtradas", {"diario", "num_lanc", "ano_cta"}, #"Outras colunas removidas1", {"diario", "num_lanc", "ano_cta"}, "Outras colunas removidas1", JoinKind.LeftOuter),
  #"Coluna Outras colunas removidas1 expandida" = Table.ExpandTableColumn(#"Consultas intercaladas", "Outras colunas removidas1", {"data_lanc", "mes", "descricao"}, {"data_lanc", "mes", "descricao"}),
  #"Linhas Filtradas" = Table.SelectRows(#"Coluna Outras colunas removidas1 expandida", each [ct_geral] <> "")
in
  #"Linhas Filtradas"

[lbendlin]

1. why are you using an ODBC connection rather than the default Sql.Database()  connector?

2. Have a fixed connection string, for example to the 2023 database.  Then in your custom queries specify a different database as needed.

[LuiSMSR]

Hello lbendlin, thank you for your response.

Why do you think I'm using ODBC connection? In my formula I have Sql.Database connector, and my data is brought by a SQL server.

I did not understand 2. Do you mean I create a parameter with value "CTA2023" and in my query i change it as needed? What I want is to have CTA+year we're at without going to the query and change it myself.  

[lbendlin]

You use a connection string, That is not necessary, it is sufficient to specify the server name.

Sql.Database - PowerQuery M | Microsoft Learn

My suggestion is to create a connection against database A  but then in your native queries you can reference database B on the same server assuming you have permissions to both databases.