Import tables with SQL Query

[Dudu24100]

Hi Everyone,

I am attempting to import tables from Snowflake using a SQL statement, specifically targeting tables that contain "PDS" in their names. The objective is to automatically import new tables that include "PDS" in their names and remove those that no longer exist in Snowflake.

Currently, I am using the following query:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'INFOMART' AND TABLE_NAME LIKE '%EU%'

However, this query returns a single table with details of all tables that contain "PDS" in their names.

Any suggestions on how to improve this query to meet my requirements would be greatly appreciated.

Tables in Snowflake :

Thank you!

[Dudu24100]

Thank you guys for your Input 🙂 

[v-dineshya]

Hi @Dudu24100 ,

Thank you for your response. Please feel free to reach out if you encounter any issues while implementing the provided workarounds. We’re happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @Dudu24100 ,

Thank you for reaching out to the Microsoft Community Forum.

You are using a SQL query to fetch metadata from INFORMATION_SCHEMA.TABLES in Snowflake. However, to dynamically import all tables that contain "PDS" in their name into Power BI, and automatically remove those that no longer exist, you will need to use dynamic import.

To filter for "PDS" tables in the INFOMART schema, please try the below SQL code.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFOMART'
AND TABLE_NAME LIKE '%PDS%'

Note: This will return all table names that contain "PDS".

I am using Power Query to Dynamically Import Tables. I am using the result of the above query as a list of table names, then loop through them dynamically to load data from each table.

Load metadata: Go to Home --> Get Data --> Snowflake. use the above SQL Query

Convert to List of Fully Qualified Table Names. In Power Query, create a new column with below codes.

= [TABLE_SCHEMA] & "." & [TABLE_NAME]

Or you can use below code.

= "EU_DATATEAM_PRO." & [TABLE_SCHEMA] & "." & [TABLE_NAME]

Loop over the tables using Table.Combine

I have created below function for example:

let
LoadPdsTable = (schema as text, name as text) =>
let
Source = Snowflake.Databases("snowflake_account", "warehouse_name"),
db = Source{[Name="EU_DATATEAM_PRO"]}[Data],
schemaObj = db{[Name=schema]}[Data],
table = schemaObj{[Name=name]}[Data]
in
table
in
LoadPdsTable

Apply function across all rows. Add a custom column that uses this function

LoadPdsTable([TABLE_SCHEMA], [TABLE_NAME])

Expand and Combine the Results

Note: Power BI doesn’t support dynamic table loading unless you refresh metadata and re-import via Power Query each time. You can automate refresh via Power BI Service. You cannot do this in DirectQuery mode due to SQL limitations with metadata-based dynamic evaluation.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[lbendlin]

Not something you can do in Power Query.  If Snowflake supports the concept of views then you can create a view there that handles the presence/absence of tables,  and then you point Power Query to that view.