Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
amitpatk
New Member

How to get dataset table name and record count dynamically

How to get dataset table name and record count dynamically in a  new table /or in directly in visualization  table 

1 REPLY 1
johnbasha33
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors