How to get register of a query to then use in another query to get the results

[mmm286b]

Hi,

I have a table where is located a table_name that is the real table where I should do the query.

Example:

Table Communication

Column DESC-> application

Column Active->Table that I need

Then I should do:

select * from Prefix"Table that I need"Sufix;

How could I do these with Power BI?

Thanks

[rajendraongole1 · Accepted solution]

Hi @mmm286b - dynamically querying a table based on a column value in another table requires a bit of creativity since Power BI does not natively support dynamic SQL queries like in SQL Server. 

Parameterize the Query in Power Query
You can use Power Query to dynamically construct a SQL query based on the column value.

Load the Communication Table:

Load the Communication table into Power BI using Power Query.
Identify the column (Active) that contains the name of the table you need to query.
Create a Parameter for the Table Name:

In Power Query, create a parameter to dynamically hold the table name.
You can set this parameter using the value of the Active column from the Communication table.

Use the Power Query Advanced Editor to construct a SQL query:

let
Source = Sql.Database("YourServerName", "YourDatabaseName"),
TableName = Table.FirstValue(CommunicationTable[Active]), // Get table name dynamically
DynamicQuery = Sql.Database("YourServerName", "YourDatabaseName", [Query="SELECT * FROM Prefix" & TableName & "Suffix"])
in
DynamicQuery

Load the dynamically queried table into Power BI.

Hope this works 

[mmm286b]

Hi,

I'm trying but it gives an error:

The main query:

let
Origen = Odbc.Query("dsn=OraceDesarrollo", "SELECT ACTIVE_NET_NAME FROM COMM;")
in
Origen

The second query:

let
    Origen = Odbc.DataSource("dsn=OraceDesarrollo", [HierarchicalNavigation=true]),
    Activa = Table.FirstValue(COMM),
    DynamicQuery = Odbc.DataSource("dsn=OraceDesarrollo", [Query="SELECT * FROM A" & Activa & "Suffix"])
in
DynamicQuery

Any help? Thanks

[mmm286b]

Problem was odbc. It's solved. Thanks