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 !!
