How do I get a dynamic list of table names, then bring back data from each?

[Kevin8]

Hi all,

I'm have a power query that searches the source database for a list of table names. Then it converts the results to a list, and gets all the column headers from each table listed. It combines these into a new table.

This lets me show all table names and the names of each column header from each in the front of a PBI.  This helps when coding sql queires to this database.  This query should also automatically update when new tables are added to the database.

My question is,  is there a way to make the query automatically generate a new query for each table in the list, so that I can bring in the top 5 rows of each table. This would give sample data for each table in the database, and illuminate better what each coulmn contains.

My current query uses a function to get all the column headers, but this M code is contained within one 'query'. How can I get Power Query to initiate a new query for each table in the list?

The query advanced editor M code is below.  As you can see part of the query does extract the top 5 rows, however I haven't been able to do anything with that. It wouldn't make sense to join it to a big table in the same way i join the column headers.

Is there a way to initiate a new query based on this retrieved data, for each step in the function?

Also, I want this query to automatically refresh in the service, adding new tables as they are created without intervention.

let

    Source = Value.NativeQuery([database]), "select * from Database_tables", null, [EnableFolding=true]),

// Select just the table I have access to view

    #"Filtered Rows" = Table.SelectRows(Source, each [schema_name] = "consume" or [schema_name] = "align"),

    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([table_name], "uvw_") or Text.Contains([table_name], "ssv")),

    // Create a string to add to a the select statement 

    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Schema_Table", each [schema_name] & "." & [table_name]),

    SchemaTableNameList = Table.Column(#"Added Custom", "Schema_Table"),

// Function to get top 5 rows from a table

    GetTop5Rows = (SchemaTableNameList as text) =>

        let

            Query = "SELECT * FROM " & SchemaTableNameList & " LIMIT 5",

            Result = Value.NativeQuery([database])

        in

            Result,

  // Apply the function to each table name

    Top5RowsList = List.Transform(SchemaTableNameList, each GetTop5Rows(_)),

    // Convert the list of tables into a record

    TablesRecord = Record.FromList(Top5RowsList, SchemaTableNameList),

    // Convert the record into a table

    TablesTable = Record.ToTable(TablesRecord),

    // Function to get column headers from a table

    GetColumnHeaders = (table as table, tableName as text) as table =>

        let

            Headers = Table.ColumnNames(table),

            AddTableName = Table.AddColumn(HeadersTable, "Table Name", each tableName),

            HeadersTable = Table.FromList(Headers, Splitter.SplitByNothing(), {"Column Header"})

        in

            AddTableName,

    // Extract column headers and combine them into one table

    CombinedHeaders = Table.Combine(List.Transform(SchemaTableNameList, each GetColumnHeaders(TablesTable{[Name=_]}[Value], _))),

    #"Reordered Columns" = Table.ReorderColumns(CombinedHeaders,{"Table Name", "Column Header"}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Table Name", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Schema", "Table"})

in

    #"Split Column by Delimiter"

[v-stephen-msft]

Hi @Kevin8 ,

Thanks for reaching out to our community.
Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[DataNinja777]

Hi @Kevin8 

To achieve the goal of dynamically creating new queries or retrieving top 5 rows from each table while ensuring automatic refresh, you can modify the query to consolidate the results into a single table rather than generating separate queries. Power Query doesn’t natively support the automatic creation of multiple queries dynamically, but you can adapt your approach by appending data into a single table that identifies the source of each row.

Your existing query can be adjusted so that instead of attempting to generate separate outputs, it retrieves the top 5 rows from each table and combines them into a unified dataset. This approach ensures that new tables added to the database are included automatically when the query refreshes.

The modified query introduces a function to dynamically retrieve the top 5 rows for each table. This function uses SQL to pull data and appends a column identifying the source table name. After applying this function to all tables in the list, the results are combined into a single table, removing null values for any inaccessible or problematic tables.

The resulting consolidated table contains all the retrieved rows, with an additional column indicating the originating table. This provides the context needed to understand which table each row belongs to, effectively acting as a catalog of sample data from the database. It avoids the need to manually create separate queries for each table and aligns well with Power BI’s refresh capabilities in the service.

This approach is practical and leverages Power Query's ability to dynamically manage and combine data while minimizing the need for manual intervention when new tables are added.

Here's the modified M code:

let
    // Fetch the list of tables
    Source = Value.NativeQuery([database], "SELECT schema_name, table_name FROM Database_tables", null, [EnableFolding=true]),
    FilteredRows = Table.SelectRows(Source, each [schema_name] = "consume" or [schema_name] = "align"),
    FilteredRows1 = Table.SelectRows(FilteredRows, each not Text.StartsWith([table_name], "uvw_") or Text.Contains([table_name], "ssv")),
    AddedCustom = Table.AddColumn(FilteredRows1, "Schema_Table", each [schema_name] & "." & [table_name]),
    SchemaTableNameList = Table.Column(AddedCustom, "Schema_Table"),

    // Function to get the top 5 rows from each table
    GetTop5Rows = (SchemaTableName as text) =>
        let
            Query = "SELECT TOP 5 * FROM " & SchemaTableName,
            Result = try Value.NativeQuery([database], Query, null, [EnableFolding=true]) otherwise null
        in
            if Result <> null then Table.AddColumn(Result, "Source Table", each SchemaTableName) else null,

    // Apply the function to all tables and combine results
    Top5RowsTables = List.Transform(SchemaTableNameList, each GetTop5Rows(_)),
    CombinedTop5Rows = Table.Combine(List.RemoveNulls(Top5RowsTables))
in
    CombinedTop5Rows

Best regards,

[Kevin8]

Thanks to @DataNinja777  your code worked brilliantly!

This presents other problems though. It produces a giant table with over 75 columns and 80 odd rows. Which isn't practical when displaying the data in the PBI front end.

How do I display only the rows and columns selected when a table is chosen?

Can you think of an easy way to do this?  I tried creating a table in dax. Showing nothing when no table is selected. Filtering to only the rows selected is easy using the "Source Table" column, but I couldn't figure out a way to only add the correct columns selected. Selecting columns by dropping any blank columns would be logical but, how do you add columns dynamically without hard coding their headers?

Many thanks again @DataNinja777    

[DataNinja777]

Hi @Kevin8 ,

To dynamically display only the rows and columns from a selected table in Power BI while avoiding a giant table with unnecessary data, you can leverage Power Query transformations combined with DAX logic and visuals. Here's how you can achieve this step by step:

Ensure the consolidated table includes only rows and columns relevant to the selected table. To dynamically handle columns, you can structure your Power Query like this:

let
    // Fetch the list of tables
    Source = Value.NativeQuery([database], "SELECT schema_name, table_name FROM Database_tables", null, [EnableFolding=true]),
    FilteredRows = Table.SelectRows(Source, each [schema_name] = "consume" or [schema_name] = "align"),
    AddedCustom = Table.AddColumn(FilteredRows, "Schema_Table", each [schema_name] & "." & [table_name]),
    SchemaTableNameList = Table.Column(AddedCustom, "Schema_Table"),

    // Function to get data for each table
    GetTableData = (SchemaTableName as text) =>
        let
            Query = "SELECT * FROM " & SchemaTableName,
            Result = try Value.NativeQuery([database], Query, null, [EnableFolding=true]) otherwise null,
            FilteredResult = if Result <> null then Table.SelectColumns(Result, List.Select(Table.ColumnNames(Result), each Table.Column(Result, _){0} <> null)) else null
        in
            if FilteredResult <> null then Table.AddColumn(FilteredResult, "Source Table", each SchemaTableName) else null,

    // Apply function and combine results
    TablesData = List.Transform(SchemaTableNameList, each GetTableData(_)),
    CombinedData = Table.Combine(List.RemoveNulls(TablesData))
in
    CombinedData

This query:

1. Filters out empty columns: Dynamically drops columns where all rows are null using Table.SelectColumns.
2. Appends a "Source Table" column: Keeps track of the originating table for each row.

Next, create a dimension table containing distinct table names from the Source Table column in Power Query or DAX. For example:

DistinctTables = DISTINCT(CombinedData[Source Table])

Filter Rows by Selected Table: Use a slicer or dropdown connected to the DistinctTables column. Create a measure to filter the rows dynamically:

FilteredRows = 
IF(
    ISFILTERED(DistinctTables[Source Table]),
    SELECTCOLUMNS(
        FILTER(CombinedData, CombinedData[Source Table] = SELECTEDVALUE(DistinctTables[Source Table])),
        "Column1", [Column1],
        "Column2", [Column2],
        ...
    ),
    BLANK()
)

Display Selected Columns Only: To avoid hardcoding column names, dynamically generate a table visual in Power BI with:

Dynamic columns based on slicer: Use SELECTEDVALUE for the Source Table and dynamically update which columns are displayed.

Then, use field parameters for dynamic column selection.

Field Parameters allow you to dynamically choose which columns are displayed in a visual based on user selection. Here’s how to set it up:

1. Go to Modeling > New Parameter > Fields.
2. Add all the potential columns from your dataset.
3. Enable the slicer for this parameter and let the user pick which columns to display.

Finally, create a dynamic table visual in Power BI

1. Place a slicer for DistinctTables[Source Table] to allow the user to select a table.
2. Use the field parameter to dynamically update the columns shown.
3. Combine the filtered rows measure with the slicer selection to dynamically adjust the rows displayed in the table.

Best regards,

[PwerQueryKees]

No, you can't create new queries from Power Query M code.

But, why don't you add a column to your table containing a table with the top5 rows?

Something like

 #"Added Top5" = Table.AddColumn(#"Added Custom", "Top5", each GetTop5Rows([Schema_Table]),