Programs
Power BI is an impressive product developed by Microsoft for the purposes of business intelligence. The application has capabilities of data visualisation and sharing insights across various platforms. It is useful in developing reports and dashboards which gives the organisation an easier way of monitoring the business and making the information-based decisions at the right time using the intelligence gained. Since the software is easy to use and integrates with other Microsoft products, it is well-adopted in all sectors.
In this blog, Power BI interview questions are presented for newbies, intermediates and experts. This guide will help you prepare for interviews by focusing on key concepts, practical examples, and common scenarios faced in Power BI interviews.
Power BI is a business intelligence and visualisation program developed by Microsoft Company. Power BI allows the user to connect to several sources of data in order to turn the unstructured data into descriptive and even graphical formats. The application integrates effectively with Excel, Azure, and other Microsoft applications, hence favourable for data geeks.
One of the core capabilities offered by Power BI is the management of extensive amounts of data and the rendering of advanced analytical solutions within the right time. More significantly, no matter the volume of data, either at a low-level reporting or performing enterprise reporting, Power BI makes it easier for organisations to explore the data. Cloud-based services help users obtain reports without regard to the place where they are situated, thus fostering collaboration among teams.
Also Read: What is Power BI?
Power BI is now becoming one of the best business intelligence tools because it is so simple to use and has great features for data presentation and visualisation. It serves as a platform for making actionable insights by reporting to the business engagingly and entertainingly.
Here’s why Power BI is a good choice:
Power BI Desktop is a Windows-based application that provides the foundation for building Power BI reports and dashboards. It allows users to:
Power BI Desktop is primarily used by data analysts and developers to create detailed, interactive reports that can later be published for broader access across an organisation.
| Feature | Power BI | Excel |
| Primary Purpose | Data visualisation and business intelligence | Spreadsheet calculations and data analysis |
| Data Volume | Can handle large datasets with ease | Performance may degrade with very large datasets |
| Visualisations | Advanced interactive visuals | Limited visual options |
| Data Modeling | Strong data modelling capabilities | Basic modelling capabilities |
Power BI consists of the following key components:
| Feature | Power BI | Tableau |
| Ease of Use | Simple drag-and-drop interface, user-friendly for beginners. | More complex but highly flexible for advanced users. |
| Cost | More affordable, with free and low-cost tiers. | Higher pricing, especially for enterprise use. |
| Data Integration | Seamless integration with Microsoft products like Excel and Azure. | Integrates well with a wide variety of sources, not limited to any ecosystem. |
| Community Support | Strong community, bolstered by Microsoft’s support. | Large, active user community with many resources. |
| Real-Time Analytics | Real-time streaming supported. | Real-time analytics are supported but a more advanced setup is required. |
Power Pivot is an Excel add-in that enables users to create data models, establish relationships, and define calculations using DAX. It allows the handling of large datasets and creates complex data models within Excel. Power Pivot is particularly useful for:
| Feature | Power Query | Power Pivot |
| Functionality | Used for extracting, transforming, and loading (ETL) data. | Focuses on creating relationships and performing data analysis. |
| Data Handling | Works with data before loading it into a model. | Works with data within a data model. |
| Primary Use | Cleans and transforms raw data. | Builds relationships and performs advanced calculations using DAX. |
| Data Size | Handles data in the transformation stage. | Suitable for working with large datasets in-memory. |
To connect to data in Power BI, follow these steps:
To publish a report in Power BI, follow these steps:
| Feature | Power BI Free | Power BI Pro |
| Cost | Free | A paid subscription is required. |
| Sharing | No sharing or collaboration. | Can share and collaborate on reports. |
| Data Capacity | Limited to 1 GB per dataset. | Supports datasets up to 10 GB. |
| Refresh | Manual refresh only. | Scheduled refresh available (up to 8 times per day). |
DAX (Data Analysis Expressions) is a formula language used in Power BI to perform data analysis and create calculated columns, measures, and tables. It is similar to Excel functions but more powerful for relational data models.
Key Features of DAX:
To calculate the total sales in Power BI, you can use the following DAX query:
Total Sales = SUM(Sales[Amount])
In this example, Sales is the table, and Amount is the column that holds the sales data. The SUM function adds up all values in the Amount column, giving the total sales.
| Feature | Power BI Desktop | Power Pivot for Excel |
| Interface | Standalone app with advanced visualisation | Excel-based add-in with limited visuals |
| Data Capacity | Supports larger datasets | Suitable for smaller datasets |
| Integration | Integrates with Power BI Service and other Microsoft services | Limited to Excel environment |
| Visualisations | Advanced, with custom and built-in visuals | Basic charting tools |
In Power BI, you can refresh data by either manually or automatically. Here are the methods:
Power BI includes a filter option which enables the user to restrict some data display by meeting some criteria so that only relevant data is retained in reports and dashboards. There are several types of filters:
| Aspect | Power BI Visuals | Custom Visuals |
| Definition | Built-in, standard visualisations provided by Power BI. | Visuals developed by third parties or users to extend functionality. |
| Availability | Available out of the box in Power BI Desktop. | Downloaded from Microsoft AppSource or created by developers. |
| Customization | Limited customization options for basic needs. | Highly customizable based on specific requirements. |
| Usage | Used for general purposes like charts, graphs, and tables. | Used for advanced needs, such as Gantt charts, bullet charts, etc. |
| Performance | Optimised for performance and usability. | Performance may vary based on development quality. |
| Support | Fully supported by Microsoft. | Support depends on the developer or community. |
Custom visuals are extensions of the pre-built functionalities of Power BI whereby users design and define graphics or visualisations to be used in the dashboard report. A developer can create these, and they can also be obtained from the Microsoft AppSource marketplace to bring diverse graphic representation possibilities to the reports.
Some examples of custom visuals include:
Custom visuals allow you to extend the functionality of Power BI and present your data in innovative and tailored ways.
| Feature | Personal Gateway | Enterprise Gateway |
| User | Single user | Multiple users |
| Data Sources | Limited to personal reports | Used for shared reports across the organisation |
| Scheduling | Manual refresh only | Scheduled refresh supported |
| Use Case | For individual use and personal reports | For organisational and collaborative use |
GetData is the feature in Power BI that allows users to import or connect to various data sources. It provides a simple interface for fetching data from multiple sources like:
Once the data is fetched, users can clean, transform, and shape it for use in Power BI reports and dashboards.
Power Query is a self-service ETL tool that helps users perform data extraction and transformation. It helps the users to perform the cleansing and modifying operations on data before making use of it for reporting or for modelling.
This is an additional column that is created in a specific table based on a particular formula or expression. As like other columns, calculated columns do not get their data from the database directly. Calculated columns, as their name suggests, contain a formula such as the DAX.
Example:
This creates a new column Full Name by combining the FirstName and LastName columns in the Employee table.
A measure is a computation that sometimes gets changed. Such methods are used in Power BI as DAX expressions. Measures mostly deal with some forms of data aggregate, be it a sum, an average, or a number, and these are computed in real-time as one uses the data.
Example:
Measures are different from calculated columns in that measures are only able to return one value per filter context, this applies to the data model and is appropriate for big data sets.
Here is an example DAX query to create a table showing total revenue by region and category:
Power BI supports a wide range of data sources:
| Aspect | Calculated Columns | Measures |
| Definition | Defined using DAX expressions as part of a table. | Defined using DAX, but are not part of the table. |
| Storage | Stored in memory as part of the data model. | Calculated on the fly based on the current context. |
| Use Case | Useful for static calculations that don’t change based on context. | Best for aggregations and dynamic calculations. |
| Performance | May use more memory as they are stored in the model. | More efficient as they are computed at runtime. |
| Filter Dependency | Do not change based on the filters applied. | Results are affected by filters and slicers in the report. |
Power BI provides two main views: Report View and Data View, each serving a distinct purpose.
To pivot a table from long format to wide format in Power Query, follow these steps:
| Aspect | Calculated Tables | Calculated Columns |
| Definition | A new table created using DAX expressions | A new column created within an existing table using DAX |
| Scope | Creates a new table in the model | Adds a column to an existing table |
| Data Storage | Stores as a separate table | Stores as part of the table |
| Use Case | For creating derived tables or summarising data | For row-level calculations or transformations |
Power Map is a complementary program of MS Excel (also known as 3D Maps) that allows individuals to depict data which has geographic and time information in three-dimensional space. In addition, while Power BI has built-in map visuals, Power Maps still comes in handy for Excel users requiring interactive maps.
To create a Power Map:
Power BI provides a wide range of visualisations to help users present data insights effectively. Here are some common types:
To calculate a running total in Power BI, a CALCULATE function with FILTER and SUM will do just fine. Below is an example of DAX measuring the running total of sales:
This formula sums up the Amount column from the Sales table. It filters the table to include all dates up to the current date in the context, ensuring the calculation accumulates over time.
Power BI Service is a SaaS application which allows users to publish, share and manage reports and dashboards created on the Power BI desktop. It also allows users to analyse data and build dashboards in real time as they access the data from wherever they are.
Relationships in Power BI Desktop are defined by linking two tables based on a common column. You can create relationships manually or automatically using the Model View, and define the relationship type (one-to-many, many-to-one), as well as set active or inactive relationships.
In Power BI, data can be stored:
Understanding Row-level Security (RLS) in Power BI allows roles to restrict certain users from accessing certain data. It allows you to create security filters that determine the filtered views of the dataset depending on the user roles or permissions.
| Feature | Row-Level Security | Object-Level Security |
| Purpose | Limits access to specific rows of data | Restricts access to entire tables or columns |
| Granularity | Row-based restrictions | Object-based restrictions |
| Use Case | Restrict users to their own data (e.g., sales region) | Hide sensitive columns or tables |
Applying general formatting to Power BI data improves clarity and readability, making reports easier to understand. It helps standardise data presentation, ensures consistent number formats, and makes visual elements more professional.
You can create a calculated column using the following DAX formula:
This concatenates the FirstName and LastName columns with a space between them.
To filter data for a specific year (e.g., 2023) and display it in a table, use the following DAX query:
This query filters the Sales table for records where the Date column’s year is 2023.
Grouping in Power BI can be done by selecting data points or categories and grouping them together manually or using DAX. You can create custom groups in visualisations or use the SUMMARIZE function to group data based on specific fields.
Bi-directional cross-filtering allows filters to flow in both directions between related tables. In Power BI, by default, relationships are single-directional, meaning filters apply from one table to another. Bi-directional cross-filtering lets both tables filter each other, which can be useful for complex models but should be used carefully to avoid performance issues or circular dependencies.
Power BI supports various data types, categorised into:
| Feature | Live Connection | Import Connection |
| Data Storage | No data is stored in Power BI; data is accessed in real-time. | Data is imported and stored in Power BI’s in-memory storage. |
| Performance | Dependent on the source system performance. | Faster performance since data is stored locally in Power BI. |
| Refresh | Real-time data updates. | Requires scheduled refreshes to update data. |
| Data Size | Suitable for large datasets, no size limits. | Limited by Power BI’s dataset size limits. |
You can create a calculated table using DAX in Power BI. For example, to create a new table from an existing Sales table, use:
This query creates a new table based on the Sales table, only including rows where the Amount is greater than 1000.
| Join Type | Description | Example Scenario |
| Inner Join | Returns records where there is a match in both tables. | Show customers who made purchases. |
| Left Join | Returns all records from the left table, and matching records from the right. | List all customers, even if there are no purchases. |
| Right Join | Returns all records from the right table, and matching records from the left. | Show all purchases, even if customers are missing. |
| Full Join | Returns all records when there is a match in either table. | Show all customers and all purchases. |
To calculate year-over-year growth, you can use the following DAX formula:
This query calculates the sales for the same period in the previous year.
| Add-in | Functionality | Use Case |
| Power Query | Extract, transform, and load (ETL) data. | Data cleaning and transformation. |
| Power Pivot | Create data models and relationships. | Handling large datasets, advanced analysis. |
| Power View | Interactive visual reports in Excel. | Creating visual dashboards. |
| Power Map (3D Maps) | Geographical data visualisation. | Plotting data on 3D maps. |
The working of Power BI can be divided into the following stages:
Power BI has two main types of gateways:
Use gateways to securely connect Power BI reports to on-premise data sources.
| Feature | Direct Query | Import Mode |
| Data Storage | Data remains in the source system. | Data is imported and stored in Power BI’s in-memory model. |
| Query Performance | Slower performance, as each interaction triggers a query to the source. | Fast, as data is stored locally. |
| Data Size | No size limitations, ideal for large datasets. | Limited by Power BI’s storage capacity. |
| Data Freshness | Real-time data queries. | Requires manual or scheduled refresh. |
Here’s a DAX query to get the top 10 customers by sales amount:
KPIs or Key Performance Indicators are the vision aspects of the contents in Power BI that specify the degree of progress towards goals over a specified period of time. KPIs normally include a target figure, an actual figure, and a status that indicates whether the target has been attained.
It is an effective solution because it provides an all-in-one solution for data analysis and reporting tools. It easily integrates with other Microsoft tools, allows instant data processing and enables non-techy users to produce remarkable reports.
Content packs are collections of dashboards, reports, and datasets that are prebuilt and shared within an organisation or offered by third parties. These enable the user to fast-track the use of reports and dashboards without the need to create them from the beginning.
| Feature | Power BI Workspace | App Workspace |
| Purpose | Personal space for individual report creation | Collaborative space for teams to share reports |
| Sharing | Limited to the user | Reports and dashboards can be shared with the team or organisation |
| Content | Reports and datasets created individually | Apps, dashboards, reports created for sharing |
This calculates the profit margin by dividing the profit by revenue.
The three fundamental concepts of DAX are:
This creates a table that shows the average sales per day.
| Aspect | Dataset | Report | Dashboard |
| Definition | The data source or model used for analysis | A multi-page document with visualisations | A single-page view of key metrics |
| Interactivity | No direct interaction | Interactive, allows users to drill down | Limited interactivity, primarily used for monitoring |
| Purpose | To provide data for reports and dashboards | To explore and visualise data | To monitor important metrics at a glance |
Power BI Desktop offers three different views to work with data and reports:
The CALCULATE function in DAX modifies the filter context of a calculation. It allows you to perform calculations with different filters applied than those set in the report visuals. Example:
This calculates the total sales where the region is “West”.
This table shows the number of orders per customer, filtered by region.
Power BI remains undoubtedly one of the most proficient tools available for data analysis, implying that organisations can easily convert any relevant information to operational data. In terms of accessibility, and integration, Power BI is the preferred intelligence system among all kinds of users.
This blog has captured some of the critical interview questions in regard to Power BI for fresh graduates, freshers with some experience and even professionals who have worked for several years. Going through these questions will assist you in acclimating yourself to interview situations that require you to explain some of the features and functions of Power BI. Looking to start your career in data science? Consider pursuing the Accelerator Program in Business Analytics and Data Science, offered by Hero Vired in collaboration with edX and Harvard University.
Accelerator Program in Business Analytics & Data Science
Integrated Program in Data Science, AI and ML
Accelerator Program in AI and Machine Learning
Certificate Program in Full Stack Development with Specialization for Web and Mobile
Certificate Program in DevOps and Cloud Engineering
Certificate Program in Application Development
Certificate Program in Cybersecurity Essentials & Risk Assessment
Integrated Program in Finance and Financial Technologies
Certificate Program in Financial Analysis, Valuation and Risk Management
© 2024 Hero Vired. All rights reserved
