Top 50 Power BI Interview Questions
Data visualization has come a long way from creating pie or graph charts in MS Excel. Since the launch of Power BI by Microsoft a few years back looking into data had never been this interactive and fun. The demand for Power BI as a business intelligence/data visualization tool has only risen in the past few years and a lot of companies hire individuals to post a thorough knowledge search of this tool via interviews.
So, here some Power BI Interview Questions that the user might find helpful on the user quest to facing the interview.
Power BI is a cloud-based data-sharing environment. Once the user has developed reports using Power Query, Power Pivot, and Power View. The user can share his/her insights with colleagues. This is where Power BI enters the equation. Power BI allows the operator to upload Excel workbooks into the Power BI cloud and share them with a chosen group of co-workers. Not only that, the colleagues can interact with the user’s reports to apply filters and slicers to highlight data. It is a simple way of sharing the user’s analysis and insights from the Microsoft cloud with the rest of the department.
Power BI features allow the user to:
Self-Service Business Intelligence (SSBI)
SSBI is a tactic to data analytics that allows trade users to filter, segment, and analyze their data, without the in-depth technical knowledge in statistical analysis, business intelligence (BI).
SSBI has made it stress-free for end-users to access their data and craft various visuals to get healthier business insights.
Anybody who has a rudimentary grasp of data can generate reports to build instinctive and shareable dashboards.
Power BI Desktop is a free desktop application that can be installed right on the user’s laptop/desktop. Power BI Desktop works together with Power BI services by offering data exploration, shaping & modeling and also, aid the user in creating a report with highly interactive visualizations. The user can save the work to a file or publish the data and reports right to the Power BI site to share with his/her colleagues.
The list of data sources for Power BI is massive, but it can be clustered into the following:
Power BI provides a variety of options to filter, report, data, and visualize. The following is the list of Filter types.
Power BI visuals have an interactive feature, which makes filtering a report easy. Visual interactions are insightful but come with some restrictions:
All of these may be used in conjunction. For example, the user might create a report on the desktop and then publish and share it online so that the colleagues could view it on their mobile devices.
Content packs are prefabricated solutions for popular features as part of the Power BI experience. A user with access can link to their profile from Power BI to see their data through live dashboards and interactive reports that have been fabricated for them. Microsoft has published content packs for services like Salesforce.com, Marketo, Adobe Analytics, Azure Mobile Engagement, CircuitID, etc.
The corporate content pack gives the employees, BI professionals, and system integrator the means to construct their content packs and to share purpose-built dashboards, reports, and datasets within their company.
Data Analysis Expressions (DAX) is a compilation of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps the user create new information from the data obtained.
To do the basic calculation and data analysis on data in power pivot, we use Data Analysis Expression (DAX). It is a formula language used to compute the calculated column and field.
We can create computed columns and measures with DAX but we cannot evaluate rows using DAX.
Below mentioned are some of the most widely used DAX function:
The FILTER function yields a table with a filter condition applied for each of its source table rows. The FILTER function is rarely used alone; it is generally used as a parameter to other functions such as CALCULATE.
FILTER is a mediator and thus can negatively impact performance over large source tables.
Complex categorizing rationality can be applied such as referencing a measure in a filter expression.
FILTER(MyTable,[SalesMetric] > 500)
Below are some of the benefits:
The solution will involve:
Alternatively, CONTAINS may be used:
Every Time a formula has a function that applies filters to identify a single row in a table, row context comes into play. When multiple filters are employed in a calculation that determines a result or value, the filter context comes into play.
Power BI datasets, MS Excel, SQL server, and analysis services.
Power BI Desktop groups the data in the user’s visuals into pieces. The user can however define the user’s groups and bins. For this use Ctrl and click to select multiple elements in the visual. Right-click on one of those elements and from the menu that appears, opt the Group. In the Groups window, the user can create new groups or modify existing ones.
On a report page, the user can resize a responsive slicer to different sizes and shapes, and the data contained in it will be rearranged to match. If a graphic becomes small to be useful, an icon representing the visual takes its place-making judicious use of space on the report page.
It is a specifically designed model that is made up of data types, tables, columns, and table relations. These data models are normally assembled for keeping data for a private business entity.
The programming behind the power pivot is the xVelocity in-memory analytics engine. It is capable of handling a significant volume of data with ease as it stores the data in the columnar database and in-memory analytics which results in faster processing of data as it loads the entire data to the RAM.
Here are some of the differences:
More than one active relationship between two tables is NOT possible. However, the user can instill more than one relationship between two tables but there will be only one active relationship and many inactive relationships.
Power query is an extract, transform & load tool used to shape data using intuitive interfaces without the need of using coding. It helps the user to:
There are majorly two destinations for output we can derive from power query:
Query folding is the procedure where the steps defined in the Power Query/Query Editor are translated into SQL and executed by the source database rather than the operators’ device. It is imperative for administering performance and scalability making the best use of the limited resources on the client’s machine.
Certainly, an SQL announcement can be defined as the source of a Power Query/M function for additional processing/logic. This is a good practice to ensure that an efficient database query is passed to the source and any unnecessary processing and complexity are avoided by the client machine and M function.
An innovative programming language is used to power a query called M-Code. It is simple to use and related to other languages. M-code is case sensitive language.
Power Query is an ETL (Extract, Transform, Load) tool that operates as an Excel add-in. It permits operators to extract data from numerous sources, manipulate the said data into a form that suits their needs, and load it on to Excel. It is the most recommended routine to use Power Query over Power Pivot as it not only allows the user to load the data but also influence it as per the user’s needs while uploading.
It is the programming language used in Power BI. It’s a functional, case-sensitive language that is a lot similar to other programming languages which are easy to implicate.
The user can configure an automatic refreshing of data daily or weekly and at different times. The user can schedule only one refresh maximum daily unless the user has Power BI Pro. In the Schedule Refresh section, use the pulldown menu selections to choose the frequency, time zone, and time of day.
Power Map can display only geographical visualizations. Thus, data about the location required e.g. city, state, country or latitude, and longitude, etc.
Power Map is an Excel add-in that provides the user with a powerful set of tools to help the user visualize and gain insight into large sets of data that have a geo-coded component. It can help the operator generate 3D visualizations by scheming up to a million data points in the form of column, heat, and bubble maps on top of a BING map. In certain scenarios, if the data is time-stamped, it can also produce interactive views that display how the data changes over time.
For data to be consumed in power map there should be location data like:
The initial requirement for the table is that it should consist of distinctive rows. It must also include location data, which can be in the form of a Latitude & Longitude, although this is not a requirement. The operator can use Street, City, Country/Region, Zip Code/Postal Code, and State/Province which can be geolocated by Bing.
Data sources may differ from being present in Excel format to multiple external sources. To prepare the user data, make sure all of the data is in an Excel table format, where each row represents a unique record. Using expressive labels also makes the value and category fields available to the user when the user designs the user tour in the Power Map Tour Editor pane.
To be able to use a table structure that precisely symbolizes time and geography inside Power Map, include all of the data in the table rows and use descriptive text labels in the column headings, like this:
Power View is a data visualization technology that allows the operator to create cooperative charts, graphs, maps, and other infographics that bring the user’s data to life. Power View is widely accessible in Excel, SharePoint, SQL Server, and Power BI.
The following provides comprehensive detail about the different visualizations available in Power View:
It is a stand-alone application where we can make Power BI reports and then upload them to Powerbi.com, it does not require Excel. It is an amalgamation of Power View, Power Pivot, and Power Query.
Yes, the user can refresh his/her reports through Data Management gateway(for SharePoint) and Power BI Personal gateway(for Powerbi.com)
There are four main types of options for a refresh in Power BI.
Package Refresh – This feature harmonizes the user’s Power BI Desktop or Excel file between the Power BI service and OneDrive or SharePoint Online. However, this does not extract data from the original data source. The dataset in Power BI will only be revised with the data in OneDrive or SharePoint Online.
Model/data Refresh – It refers to refreshing the dataset in the Power BI service with data from the original data source. This is done by either using scheduled refresh/refresh now. It requires access to on-premises data sources.
Tile Refresh – Tile refresh updates the cache for tile visuals on the dashboard once data alters. This happens every fifteen minutes. The user may also instigate a tile refresh by selecting the 3 dots in the upper right-hand corner of a dashboard and selecting Refresh dashboard tiles.
Visual Container Refresh – Refreshing the visual container revises the cached report graphics within a report once the data modified.
Power BI is NOT accessible as a confidential core cloud service. However, with Power BI and Power BI Desktop, the user can securely connect to the users’ on-premises data sources. With the On-premises Data Gateway, the user can connect live to the other users’ on-premises SQL Server Analysis Services and other data sources. The user can also schedule refresh with a centralized gateway. If a gateway is not available, the user can refresh data from on-premises data sources using the Power BI Gateway – Personal.
The gateway acts as a bridge between on-premises data sources and Azure cloud services.
Power BI Q&A is a natural language tool that helps in querying the user data and get the results the user needs from it. The user attains this by typing into a dialog box on the Dashboard, in which the engine instantaneously generates an answer similar to in Power View. Q&A interprets the user questions and shows the user a restated query of what it is looking for from the user data. Q&A was developed by Server and Tools, Microsoft Research, and the Bing teams to give the user a complete feeling of truly exploring the user data.
Below are some of the ways through which we can influence Power BI:
The Power BI Publisher for Excel:
Calculated Columns are DAX expressions that are calculated during the model’s processing/refresh process for each row of the given column and can be used like any other column in the model.
Calculated columns are not compressed and thus require additional memory and result in decreased query performance. They can also decrease processing/refresh performance if applied on large amounts of data and can make a model more difficult to maintain/support given that the computed column is not present at the source.
Many to Many relationships involves building a link between tables reflecting the combinations of two dimensions (e.g., doctors and patients).
The 3 edit communication choices are Filter, Highlight, and None.
Ms-Excel, VBA & MySQL
Using PowerBI &Tableau