How to get dataset table name and record count dynamically

Hi amitpatk  

In Power BI, you can achieve this by using Power Query (M language) to dynamically retrieve the dataset table names and record counts, and then create a new table or visualization based on that information. Here's a step-by-step guide:

1. **Connect to Data Source**: Connect to your database or data source using Power BI.

2. **Retrieve Dataset Information**: Use Power Query to write a custom query to retrieve table names and record counts dynamically.

3. **Transform Data**: Transform the retrieved data as needed, such as renaming columns or formatting data types.

4. **Load Data**: Load the transformed data into Power BI.

5. **Create Visualization (Optional)**: Create a visualization based on the loaded data to visualize the record counts by table.

Here's a more detailed guide:

1. **Connect to Data Source**:
- Open Power BI Desktop.
- Go to the "Home" tab and click on "Get Data".
- Choose your data source (e.g., SQL Server, MySQL, etc.) and connect to it.

2. **Retrieve Dataset Information**:
- In Power Query Editor, navigate to the "Home" tab and click on "Advanced Editor".
- Write a custom query to retrieve table names and record counts. For example, in SQL Server, you can use a query like:
```sql
SELECT TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
```
- Click on "Done" to close the Advanced Editor and apply the query.

3. **Transform Data**:
- In Power Query Editor, you can perform any necessary transformations such as renaming columns or changing data types.

4. **Load Data**:
- Once you're done with transformations, click on "Close & Load" to load the data into Power BI.

5. **Create Visualization (Optional)**:
- In the report view, you can create a new table or chart visualization based on the loaded data to visualize the record counts by table. For example, you can create a table visualization with table names and record counts.

By following these steps, you can dynamically retrieve dataset table names and record counts in Power BI and create a new table or visualization based on that information.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!