How to Query Sys.Tables to find the table name

[Anonymous]

Hello,

In PowerBi Advanced Editor, I want to run the sys.tables quuery first to get the table name.  Then I want to query that table.  Is this possible?

SELECT name

FROM sys.tables

WHERE create_date = (

SELECT Max(create_date) NEWEST_VERSION

FROM sys.tables

WHERE name like '%AvailabilityStatus%'

)

[OwenAuger · Accepted solution]

Hi @Anonymous 

Yes this is possible.

I would suggest modifying your query to include the Schema as well to avoid ambiguity.

Then use the results of the first query as inputs into a second query.

Try this M code in a new Power Query query:

(replace Server and Database values with appropriate values)

let
    Server = ".\SQLEXPRESS",
    Database = "OwenSandbox",
    TableList = Sql.Database(Server, Database, [Query="SELECT tables.name AS table_name#(lf)#(tab),schemas.name AS schema_name#(lf)FROM sys.tables AS tables#(lf)LEFT OUTER JOIN sys.schemas AS schemas ON tables.schema_id = schemas.schema_id#(lf)WHERE create_date = (#(lf)#(tab)#(tab)SELECT Max(create_date) NEWEST_VERSION#(lf)#(tab)#(tab)FROM sys.tables#(lf)#(tab)#(tab)WHERE name LIKE '%AvailabilityStatus%'#(lf)#(tab)#(tab))#(lf)", CreateNavigationProperties=false]),
    TableName = TableList[table_name]{0},
    SchemaName = TableList[schema_name]{0},
    NavigateToTable = Sql.Database(Server, Database){[Schema = SchemaName, Item=TableName]}[Data]
in
    NavigateToTable

Regards

[OwenAuger · Accepted solution]

Hi @Anonymous 

Yes this is possible.

I would suggest modifying your query to include the Schema as well to avoid ambiguity.

Then use the results of the first query as inputs into a second query.

Try this M code in a new Power Query query:

(replace Server and Database values with appropriate values)

let
    Server = ".\SQLEXPRESS",
    Database = "OwenSandbox",
    TableList = Sql.Database(Server, Database, [Query="SELECT tables.name AS table_name#(lf)#(tab),schemas.name AS schema_name#(lf)FROM sys.tables AS tables#(lf)LEFT OUTER JOIN sys.schemas AS schemas ON tables.schema_id = schemas.schema_id#(lf)WHERE create_date = (#(lf)#(tab)#(tab)SELECT Max(create_date) NEWEST_VERSION#(lf)#(tab)#(tab)FROM sys.tables#(lf)#(tab)#(tab)WHERE name LIKE '%AvailabilityStatus%'#(lf)#(tab)#(tab))#(lf)", CreateNavigationProperties=false]),
    TableName = TableList[table_name]{0},
    SchemaName = TableList[schema_name]{0},
    NavigateToTable = Sql.Database(Server, Database){[Schema = SchemaName, Item=TableName]}[Data]
in
    NavigateToTable

Regards

[Anonymous · Accepted solution]

It worked perfectly.   I have never been so appreciative!!!!!  Thank you.  Getting this little query to work is saving us from having to build a bunch of other things planned.