Expert Edge Session Summary: From understanding fact and dimension tables to data modeling and Power Query transformations, this masterclass covers the non-negotiables every Power BI user must know. Whether you are preparing for an interview or looking to improve your data analysis skills, this session equips you with practical strategies to excel in Power BI.
Meet the Expert: Anmol Lohia is a renowned Power BI expert and data visualization trainer. With over 15 years of experience, he has worked with global organizations, delivering impactful training sessions and empowering professionals to transfer road raw data into actionable insights recognized as a national faculty. At ICAI, he has trained 10,000+ professionals on Power BI, MS Excel, and AI-driven automation.
In today’s data-driven world, visualization and analytics are key to making informed business decisions. Power BI is a powerful business intelligence tool from Microsoft. It enables professionals to transform raw data into compelling visual narratives.
In a recent Shine.com Expert Edge session, Anmol Lohia, a renowned Power BI expert and data visualization trainer, shared invaluable insights on Power BI’s potential. Let’s dive into the essentials he covered in this session.
The Hidden Backbone of Power BI Dashboards
We often admire sleek, interactive dashboards on business reports, but what remains unseen is the meticulous data preparation behind them. According to Anmol, visualization only constitutes 30-35% of the work. The real effort lies in cleaning and structuring data effectively. Understanding the backend is critical to building impactful dashboards.
Key Power BI Concepts Explained
1. Fact Tables vs. Dimension Tables
- A fact table contains transactional data. In the session’s case study, the sales register served as the fact table, logging every sale, including order numbers, customer IDs, and product details.
- Dimension tables, such as customer data, salesperson details, and product information, provide contextual details to enhance the analysis.
2. Understanding Data Cleaning with Power Query
- Real-world data is rarely clean. Power Query, the backend of Power BI, plays a crucial role in transforming messy data into structured formats.
- Power Query allows automation in data cleaning, reducing repetitive tasks.
- Data type consistency is essential: numbers should be in number format, dates in date format, and text in text format.
3. Distinguishing Between Distinct and Unique Values
- Distinct values represent all different items in a column.
- Unique values are those that appear only once.
- Understanding this concept ensures accurate data analysis and prevents duplication errors.
4. Building Relationships with Data Modeling
- Relationships in Power BI function similarly to VLOOKUP in Excel.
- Fact tables and dimension tables should be connected through primary keys (e.g., linking Product ID in the sales register with Product ID in the product table).
- A 1-to-many relationship is ideal: one unique product in the product table can appear multiple times in the sales register.
How to download Power BI desktop?
Power BI Desktop is Freely Available. It can be downloaded for free from the Microsoft Store.
Downloading Steps
- Open the Microsoft Store on your Windows PC.
- Search for “Power BI Desktop”.
- Click Download and install the application.
Important Note for Mac Users: Power BI Desktop is not available for Mac users. Currently, it only supports Windows OS.
How to Pull Data into a Power Query?
Here’s the step-by-step process Anmol Lohia explained on how to pull data into Power Query:
1. Open Power BI Desktop
2. Access Power Query
- Click on the Home tab.
- Select Transform Data—this opens the Power Query Editor.
3. Import Data from an Excel File
- Close any open Excel files containing the data.
- Copy the file path where the Excel file is saved.
- In Power Query, click New Source and choose Excel Workbook.
- Browse and select the Excel file.
4. Choose Tables to Import
- A Navigator tab will appear, displaying available tables and sheets.
- Select the tables needed for analysis (instead of entire sheets for better structure).
- Click OK to import the data into Power Query.
5. Explore Data Sources
- Power Query supports multiple data sources beyond Excel, including SQL servers, JSON files, PDFs, and even images.
- This flexibility makes Power BI a powerful tool for real-time data integration.
6. Automated Data Cleaning
- Any data cleaning performed in Power Query is recorded as a step, allowing for automation in future updates.
- Once the data is cleaned and structured, click Close & Apply to load it into Power BI for visualization.
Practical Power BI Hacks for Efficiency
- Shortcut to find columns: Press Ctrl + G to quickly locate specific columns.
- Quick filtering: Right-click on any value and select Text Filters > Equals To for instant data filtering.
- Undo actions in Power Query: The Query Settings panel records all transformation steps, allowing users to revert changes seamlessly.
Expert’s Reply to Audience’s Queries
Q1: What is the difference between Cost and Revenue DAX formulas?
Answer:
The formula for revenue is simply multiplying quantity by price. The formula for cost is multiplying quantity by cost. That is the only difference—everything else remains the same.
Q2: What are the most common interview questions for Power BI?
Answer:
For freshers, expect questions on:
- Basics of Power Query (e.g., splitting columns, identifying unique and distinct values).
- Fact tables and dimension tables.
- Data modeling concepts and why relationships between tables matter.
- Sorting and filtering data in Power BI.
For experienced professionals, questions focus on:
- Advanced DAX functions and calculations.
- Understanding data modeling structures (e.g., Star Schema, Snowflake Schema).
- Optimization techniques for handling large datasets efficiently.
- Using M Query for advanced data transformation.
Q3: How do we visualize the top 5 customers in Power BI?
Answer:
1. Insert a Clustered Bar Chart.
2. Set Customer Name on the Y-axis.
3. Set Revenue on the X-axis.
4. Use a filter to display only the top 5 customers based on revenue.
Q4: What should we keep in mind when dealing with large datasets (crores of records)?
Answer:
- Optimize the data model by keeping only essential columns.
- Use aggregations instead of detailed raw data for performance improvements.
- Create a calendar table for better date filtering and performance optimization.
- Ensure query dependencies are minimal to avoid redundant calculations.
Q5: Can we modify incorrect data directly in Power BI?
Answer:
- Direct modification of source data is not recommended as it can compromise audit trails.
- Instead, use Power Query’s “Replace Values” option to correct errors while maintaining a track record.
Final Thoughts: Mastering the Art of Data Storytelling
Anmol emphasized that mastering Power BI goes beyond simply creating dashboards—it’s about understanding data quality, relationships, and automation. By focusing on the backend, professionals can ensure their visualizations are accurate, scalable, and impactful.
As businesses increasingly rely on data, Power BI expertise becomes a valuable asset for professionals looking to elevate their analytical skills. Whether you’re preparing for an interview or improving reporting efficiency at work, mastering these non-negotiable Power BI concepts is a game-changer.
To watch the complete session and grasp a better understanding of Power BI, click
