Tableau Training and Certification
- 28k Enrolled Learners
- Live Class
In this Power BI Interview Questions blog, we will be discussing some of the most important interview questions associated to Power BI certification which will help you stand out in your interview.
Power BI came into existence in late 2013 after Microsoft had decided to combine multiples excel add-ons to create a complete new and independent tool. Power BI has been the major contributing factor for Microsoft’s growth in the domain of Business Intelligence and Data Visualization in the year 2016-17. Below image is the change in positions of organizations in the Data Visualization domain as per Gartner:
As you can see there is a major moment of Microsoft in the year 2017 when compared to 2016 and this is mainly due to the contribution oferal Power BI. In case you are still not convinced about moving into the Power BI domain, below image will give you an idea about the current market for Power BI:
In this Power BI interview questions blog, I have collected the most frequently asked questions by interviewers. These questions are collected after consulting with top industry experts in the field of Data analytics and visualization. If you want to brush up with the Power BI basics, you can take a look at this Power BI Tutorial blog. You can even get a Power BI certification after the course. Click below to know more.
So, here are the Top 50 Power BI Interview Questions and Answers which are most likely to be asked by the interviewer. For your ease of access, I have categorized the Power BI interview questions namely:
Ans: Microsoft has two parts for Self-Service BI
|Excel BI Toolkit||It allows users to create an interactive report by importing data from different sources and model data according to report requirement.|
|Power BI||It is the online solution that enables you to share the interactive reports and queries that you have created using the Excel BI Toolkit.|
Ans: Self-Service Business Intelligence (SSBI)
Ans: Power BI is a cloud-based data sharing environment. Once you have developed reports using Power Query, Power Pivot and Power View, you can share your insights with your colleagues. This is where Power BI enters the equation. Power BI, which technically is an aspect of SharePoint online, lets you load Excel workbooks into the cloud and share them with a chosen group of co-workers. Not only that, but your colleagues can interact with your reports to apply filters and slicers to highlight data. They are completed by Power BI, a simple way of sharing your analysis and insights from the Microsoft cloud.
Power BI features allow you to:
Ans: Power BI Desktop is a free desktop application that can be installed right on your own computer. Power BI Desktop works cohesively with the Power BI service by providing advanced data exploration, shaping, modeling, and creating report with highly interactive visualizations. You can save your work to a file or publish your data and reports right to your Power BI site to share with others.
Ans: The list of data sources for Power BI is extensive, but it can be grouped into the following:
Ans: The following are the Building Blocks (or) key components of Power BI:
Ans: Power BI provides variety of option to filter report, data and visualization. The following are the list of Filter types.
We know that Power BI visual have interactions feature, which makes filtering a report a breeze. Visual interactions are useful, but they come with some limitations:
Ans: Content packs for services are pre-built solutions for popular services as part of the Power BI experience. A subscriber to a supported service, can quickly connect to their account from Power BI to see their data through live dashboards and interactive reports that have been pre-built for them. Microsoft has released content packs for popular services such as Salesforce.com, Marketo, Adobe Analytics, Azure Mobile Engagement, CircuitID, comScore Digital Analytix, Quickbooks Online, SQL Sentry and tyGraph.
Organizational content packs provide users, BI professionals, and system integrator the tools to build their own content packs to share purpose-built dashboards, reports, and datasets within their organization.
Ans: To do basic calculation and data analysis on data in power pivot, we use Data Analysis Expression (DAX). It is formula language used to compute calculated column and calculated field.
Sample DAX formula syntax:
For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount] column in the Sales table.
A- Measure Name
B- = – indicate beginning of formula
C- DAX Function
D- Parenthesis for Sum Function
E- Referenced Table
F- Referenced column name
Ans: Below are some of the most commonly used DAX function:
Ans: The FILTER function returns a table with a filter condition applied for each of its source table rows. The FILTER function is rarely used in isolation, it’s generally used as a parameter to other functions such as CALCULATE.
Ans: These are the only functions that allow you modify filter context of measures or tables.
Ans: Below are some of the benefits:
Ans: The solution will involve:
Alternatively, CONTAINS may be used:
Ans: Below are the most important BI add-in to Excel:
Ans: Power Pivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook. It lets you create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts. You can then further analyze the data so that you can make timely business decisions without requiring IT assistance.
Ans: It is a model that is made up of data types, tables, columns, and table relations. These data tables are typically constructed for holding data for a business entity.
Ans: The main engine behind power pivot is the xVelocity in-memory analytics engine. It can handle large amount of data because it stores data in columnar databases, and in memory analytics which results in faster processing of data as it loads all data to RAM memory.
Ans: Here are some of the differences:
Ans: No, we cannot have more than one active relationship between two tables. However, can have more than one relationship between two tables but there will be only one active relationship and many inactive relationship. The dotted lines are inactive and continuous line are active.
Ans: Power query is a ETL Tool used to shape, clean and transform data using intuitive interfaces without having to use coding. It helps the user to:
Ans: There are two destinations for output we get from power query:
Ans: Query folding is when steps defined in Power Query/Query Editor are translated into SQL and executed by the source database rather than the client machine. It’s important for processing performance and scalability, given limited resources on the client machine.
Ans: Changing Data Types, Filtering Rows, Choosing/Removing Columns, Grouping, Splitting a column into multiple columns, Adding new Columns ,etc.
Ans: Yes, a SQL statement can be defined as the source of a Power Query/M function for additional processing/logic. This would be a good practice to ensure that an efficient database query is passed to the source and avoid unnecessary processing and complexity
by the client machine and M function.
Ans:Query parameters can be used to provide users of a local Power BI Desktop report with a prompt, to specify the values they’re interested in.
Parameters and templates can make it possible to share/email smaller template files and limit the amount of data loaded into the local PBIX files, improving processing time and experience .
Ans: A new programming language is used in power query called M-Code. It is easy to use and similar to other languages. M-code is case sensitive language.
Ans: Power Query is a self-service ETL (Extract, Transform, Load) tool which runs as an Excel add-in. It allows users to pull data from various sources, manipulate said data into a form that suits their needs and load it into Excel. It is most optimum to use Power Query over Power Pivot as it lets you not only load the data but also manipulate it as per the users needs while loading.
Ans: Power Map is an Excel add-in that provides you with a powerful set of tools to help you visualize and gain insight into large sets of data that have a geo-coded component. It can help you produce 3D visualizations by plotting upto a million data points in the form of column, heat, and bubble maps on top of a Bing map. If the data is time stamped, it can also produce interactive views that display, how the data changes over space and time.
Ans: For a data to be consumed in power map there should be location data like:
The primary requirement for the table is that it contains unique rows. It must also contain location data, which can be in the form of a Latitude/Longitude pair, although this is not a requirement. You can use address fields instead, such as Street, City, Country/Region, Zip Code/Postal Code, and State/Province, which can be geolocated by Bing.
Ans: The data can either be present in Excel or could be present externally. To prepare your data, make sure all of the data is in Excel table format, where each row represents a unique record. Your column headings or row headings should contain text instead of actual data, so that Power Map will interpret it correctly when it plots the geographic coordinates. Using meaningful labels also makes value and category fields available to you when you design your tour in the Power Map Tour Editor pane.
To use a table structure which more accurately represents 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:
In case you wish to load your data from an external source:
Ans: Power View is a data visualization technology that lets you create interactive charts, graphs, maps, and other visuals which bring your data to life. Power View is available in Excel, SharePoint, SQL Server, and Power BI.
The following pages provide details about different visualizations available in Power View:
Ans: It is a stand alone application where we can make Power BI reports and then upload it to Powerbi.com, it does not require Excel. Actually, it is a combination of Power Query, Power Pivot, and Power View.
Ans: Yes we can refresh our reports through Data Management gateway(for sharepoint), and Power BI Personal gateway(for Powerbi.com)
Ans: There are four main types of refresh in Power BI. Package refresh, model or data refresh, tile refresh and visual container refresh.
This synchronizes your Power BI Desktop, or Excel, file between the Power BI service and OneDrive, or SharePoint Online. However, this does not pull data from the original data source. The dataset in Power BI will only be updated with what is in the file within OneDrive, or SharePoint Online.
It referrs to refreshing the dataset, within the Power BI service, with data from the original data source. This is done by either using scheduled refresh, or refresh now. This requires a gateway for on-premises data sources.
Tile refresh updates the cache for tile visuals, on the dashboard, once data changes. This happens about every fifteen minutes. You can also force a tile refresh by selecting the ellipsis (…) in the upper right of a dashboard and selecting Refresh dashboard tiles.
Refreshing the visual container updates the cached report visuals, within a report, once the data changes.
To know more about data refresh and understand how to implement data refresh, you can check the following link.
Ans: No, Power BI is not available as a private, internal cloud service. However, with Power BI and Power BI Desktop, you can securely connect to your own on-premises data sources. With the On-premises Data Gateway, you can connect live to your on-premises SQL Server Analysis Services, and other data sources. You can also scheduled refresh with a centralized gateway. If a gateway is not available, you can refresh data from on-premises data sources using the Power BI Gateway – Personal.
Ans: Gateway acts a bridge between on-premises data sources and Azure cloud services.
Ans: Power BI Q&A is a natural language tool which helps in querying your data and get the results you need from it. You do this by typing into a dialog box on your Dashboard, which the engine instantaneously generates an answer similar to Power View. Q&A interprets your questions and shows you a restated query of what it is looking from your data. Q&A was developed by Server and Tools, Microsoft Research and the Bing teams to give you a complete feeling of truly exploring your data.
Ans: Below are some of the ways through which we can leverage Power BI:
Ans: Calculated Columns are DAX expressions that are computed 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 consume more memory and result in reduced query performance. They can also reduce processing/refresh performance if applied on large fact tables and can make a model more difficult to maintain/support given
that the calculated column is not present in the source system.
Ans: Power BI can apply Row Level Security roles to models.
Ans: Many to Many relationships involve a bridge or junction table reflecting the combinations of two dimensions (e.g. doctors and patients). Either all possible combinations or those combinations that have occurred.
Ans: There are mainly 2 reasons why we would have tables without relations in our model:
Ans: You can use Power BI publisher for Excel to pin ranges, pivot tables and charts to Power BI.
The Publisher installs all necessary drivers on local machine to establish connectivity .
Ans: Dataset: The source used to create reports and visuals/tiles.
Report: An individual Power BI Desktop file (PBIX) containing one or more report pages.
Dashboard: a collection of visuals or tiles from different reports and, optionally, a pinned.
Ans: The 3 edit interaction options are Filter, Highlight, and None.
Filter: It completely filter a visual/tile based on the filter selection of another visual/tile.
Highlight: It highlight only the related elements on the visual/tile, gray out the non-related items.
None: It ignore the filter selection from another tile/visual.
Ans: With a data model local to the PBIX file (or Power Pivot workbook), the author has full control over the queries, the modeling/relationships, the metadata and the metrics.
With a live connection to an Analysis Services database (cube) the user cannot create new metrics, import new data, change the formatting of the metrics, etc – the user can only use the visualization, analytics, and formatting available on the report canvas.
With a direct query model in Power BI to SQL Server, for example, the author has access to the same features (and limitations) available to SSAS Direct Query mode.
Ans: Below are some of the way through which SSRS can be integrated with Power BI:
I hope this set of Power BI Interview Questions and Answers will help you in preparing for your interviews. All the best!
If you wish to learn Power BI and build a career in data visualization or BI, then check out our Power BI Training Certification which comes with instructor-led live training and real-life project experience. This training will help you understand Power BI in depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section and we will get back to you