Import tables with SQL Query

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.

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.