Advanced Power BI Interview Questions and Answers [2019]

After completing the first part of Power BI interview questions, let’s move ahead with the advanced Power BI interview questions and answers. This Power BI interview preparation guide covers a bit complex interview questions and answers of Power BI.

I assure you that after the completion of both parts of Power BI interview questions and answers, you will not require any other kind of preparation material. So, let’s quickly wrap up your interview preparation with some Microsoft Power BI Interview Questions and Answers.

Top Power BI Interview Questions and Answers

Top Power BI Interview Questions and Answers

Here are the Power BI interview questions and answers for experienced professionals to take your preparation to the next level:

Q.1. What is SSRS? How is it different from Power BI?

Ans. SSRS stands for SQL Service Reporting Service. It is a server-based reporting generating platform. SSRS is a part of Microsoft’s SQL Server suite. It is similar to Power BI in a lot of ways, but it is more of a conventional tool. SSRS is mainly used for enterprise visualization of data and generating, viewing and sharing reports. It has a programming interface with an integrated set of processing components. SSRS has less graphical, drag and drop features and more programming-based tools for the report making. Due to this, users need to put in more manual efforts.

Here are some basic differences between Power BI and SSRS:

  • Power BI is free, SSRS requires SQL Server license. However, you require purchasing Power BI Premium license for Power BI Report Server.
  • Power BI cloud-based while SSRS is server-based.
  • Power BI is more modern and graphical whereas, SSRS is conventional and programming based. SSRS requires manual effort and time to create reports and do analysis.
  • We can use data from the cloud in Power BI. With SSRS, we can only use on-premise data.
  • Power BI now has Cortana integrated for AI-based natural language, Q&A about your data and reports in Power BI. SSRS does not have this feature.
  • In SSRS, you need to purchase a license, define requirements and scope, customize your software, deploy, develop reports in a coding interface, schedule data refreshes, etc.
  • Power BI has a graphical interface with the drag-and-drop capability to create reports. You can work with unstructured data, use modern rendering, publish, integrate and collaborate easily across platforms.

Check out a dedicated article on Power BI vs SSRS and find out the differences in detail

Q.2. What are paginated reports?

Ans. A paginated report is an organized, document style page to page report. Paginated reports are saved as paged documents where we can expand the document vertically and horizontally to view the complete data. These reports are pixel-perfect with a fixed layout. They can be the best options when we want to print a report from a PDF or Word file. The extension used for paginated files are .RDL.

Q.3. What are ‘Measures’ in Power BI? How can we create them?

Ans. Measures are formulas that are calculated to yield results of complex queries (specific to the purpose and values it is used for). Generally, measures are used in aggregational calculations such as sum, average, minimum value, maximum value, counts, etc. The resultant values are in RAM storage because measures are used relatively and may have different values when used in different scenarios and contexts.

Measures in Power BI are used to create advanced solutions and models for data analysis and reporting. They perform real-time calculations based on your interactions with data on your report.

We can create new measure fields for analyzing in Power BI Desktop easily. We can also create a new measure field using New measure option and select this option in two ways:

  • One, by right-clicking on the table’s name in which we wish to add the measure field and select New measure.
  • Second, by going to the Modeling tab and clicking on New measure option.

Then in the formula bar, we need to enter the name of our measure and write the expression for the measure.

Q.4. What do you understand by the Power BI Gateway?

Ans. Power BI Gateway is a software that is used to access data situated in an on-premises network from the cloud. Gateways act as a gatekeeper for the on-premises data source. If anyone needs to access on-premises data from a cloud or web-based app, the request goes through the gateway. All the connection requests are attended by the gateway and access is granted based on the user’s authentication and requirements.

Gateways do not transfer data from the on-premises source to the client platform. It only connects that platform directly to the on-premises data source for the clients to directly access data from it. Gateways are used to facilitate connections between a single data source or multiple data source to the on-premises data source.

Don’t forget to check – Types and Working of Power BI Gateway

Q.5. What is Bidirectional cross-filtering in Power BI?

Ans. The bidirectional cross-filtering feature enables a free flow of filtering conditions between data tables. In Power BI, some data models are simple, with a star schema type association. Whereas some data models are complex having tables with many to many relationships. Thus, by using the bidirectional cross-filtering feature, a modeler can let the filtering conditions be applied on both sides of a relationship or association. The filters have a certain direction and can be applied to values in different tables according to the relationship that the tables share with each other. Bidirectional cross-filtering gives a lot of flexibility to the data modelers to create models and apply filtering conditions on data tables with many to many relationships.

Q.6. What is Power BI Designer?

Ans. Power BI Designer is a stand-alone companion application of Power BI. It consists of Power Query, Power Pivot and Power View, all together in one application. Power BI Designer is used to create Power BI reports and other elements and to work with data. We can download Power BI Designer from the Power BI Site. The reports created in Power BI Designer can be later published on the Power BI Service.

Q.7. What is DAX in Power BI?

Ans. DAX stands for Data Analysis Expressions. It is a formula language with such expressions or formulas that are used for data analysis and calculations. These expressions are a combination of functions, operators, and constants. The expression is evaluated as one formula that yields appropriate results (value or values). With the help of the DAX language, analysts can discover new ways to calculate data values they have and come up with fresh insights. Power BI Desktop offers a library having about 200 DAX functions to perform complex calculations.

DAX formula syntax and sample formula:

DAX Syntax - Power BI interview questions

A. Name of the new measure
B. An operator indicating the start of the DAX formula and equating the two sides
C. DAX function
D. The parenthesis (). Used to enclose and define arguments.
E. Reference table name
F. Reference column name
G. Another operator

A sample DAX formula is:

ProjectedSales2019=SUM(Sales[TotalSales2018])*1.08

This DAX formula commands the system to calculate the product of the sum of TotalSales2018 and 1.08 (8% increase) and store the value in a new field or column known as ProjectedSales2019.

You must definitely complete the Power BI DAX before proceeding ahead

Q.8. What is Power Query? Which language is used in the Power Query?

Ans. Power Query is an Excel add-on. It is a self-service ETL tool (Extract, Transform and Load) which is used to import data, transform, shape/clean data and load it for analysis. Power Query provides an interactive user interface to manage and work with data. We can import data from multiple data sources such as Big Data, files, databases, social media, etc. We can join data from different sources and make one single dataset. Also, we can transform the data by adding, removing and shaping our datasets as per our requirement.

The programming language used in Power Query Editor is known as M-code. It is easy to learn case sensitive language.

Q.9. What are some key points about DAX usage in Power BI?

Ans. Some key points about DAX:

  • DAX is a functional language. That means, its complete code is always a function.
  • An executable DAX expression generally contains conditional statements, nested functions, value references, etc.
  • DAX formulas have two primary data types; Numeric and Non-numeric or Others. The numeric data type includes integers, decimals, currency, etc. And, the non-numeric consists of strings and binary objects.
  • DAX expressions are evaluated in an inward to outward fashion. That is, it starts from the innermost function going to the outermost one at the very last. We should formulate our DAX expressions in proper order so that the system reads it properly.
  • We can use values of mixed data types as inputs in a DAX formula. Data type conversion will take place automatically during execution of the formula.

Q.10. Which table functions will you use to group data in Power BI?

Ans. There are two main DAX functions which we can use to group data in Power BI; SUMMARIZE() and SUMMARIZECOLUMNS:

  • The SUMMARIZE function is used to group data by columns.
  • The SUMMARIZECOLUMNS function is generally used in Power BI to group data by columns, expressions, and tables.

Q.11. What are query parameters? What are Power BI Templates?

Ans. Query parameters are the parameters which we can use as/in queries in Power BI query editor. We can create new parameters in Power BI Desktop and later use them in queries, data models and reports. Query parameters contain a selected set of data values from the entire dataset.

Power BI Templates are data-less PBIX files, that is, empty Power BI templates in which we can use our data. Such PBIX files are exported as template files (PBIT) via share options or emails so that others can import their data into them without having to create a template.

Q.12. Explain the Power Pivot. How is it useful for Power BI?

Ans. Power Pivot is an Excel add-in which is used to collect data from multiple data sources, create datasets and use that data for analysis and drawing insights for business and decision making. We can perform a lot of activities in Power Pivot:

  • Import millions of rows of data into a single Excel workbook.
  • Create relationships between heterogeneous data tables and rows.
  • Create calculated columns and measures by using queries and formula.
  • Build PivotCharts and PivotTables.

The charts and tables created in Power Pivot can be used in Power BI for further analysis and report making.

Q.13. What is a Power Pivot data model? Which analytics engine used in Power Pivot?

Ans. The data models are the relationships formed between different data tables to structure the information properly. A data model will have defined data types, columns, associations, tables, relations, etc. The data models in the Power Pivot only support single direction relationships that are one-to-many and calculated columns rather than calculated tables in Power BI.

Power Pivot currently uses the SSAS in-memory Vertipaq compression engine. The data models are stored in the in-memory of the client computer.

Q.14. How many active relationships can we have between two tables in Power Pivot data model?

Ans. We can have more than one relationship between tables in a Power Pivot data model. But there can be only one active relationship at a time in the data model between two tables. While one of the many relationships are active, the other relationships remain in an inactive state. In the Power Pivot data model, active relationships are indicated by a continuous line and inactive relationships by dotted lines.

Q.15. Tell us about “Query folding” in Power Query.

Ans. Query folding is the process of converting or translating the code in Power Query Editor into SQL. Query folding is needed when codes or queries are getting executed by the source database instead of the client machine. This happens when there are limited resources on the client machine. It also helps in performance optimization and scaling.

Q.16. What do you know about the transformation in Power Query Editor?

Ans. The Power Query Editor enables us to import data from multiple sources and transform it in many ways as per our requirements. Some general data transformation practices in Power Query Editor are:

  • Change data types of columns.
  • Rename columns.
  • Replace values.
  • Fix errors in Any Column group.
  • Working with the nested data in Structured Column groups.
  • Add custom columns, conditional columns, add measures, etc.
  • Run R Queries directly on Power BI Query Editor by using Run R Script function.

Q.17. Why should we use SQL code in Power Query editor?

Ans. It is good to use SQL codes in Power Query editor along with M-code when we need to explain complex logic or want to add some additional processes in the query. SQL statements simplify the M function and pass on a bug-free, an easy-to-read query to the source database from the client machine.

Q.18. Can you tell us why do we need Power Query when we already have Power Pivot to import data?

Ans. Power Query is a self-service Excel add-in, which is used to import data from different sources, transform or manipulate it and then load it into Excel. It is a typical ETL tool which is loaded with functionalities and tools to extract, transform and load data from different kinds of data sources to create single datasets. Whereas Power Pivot is also an Excel add-in, but it does not focus on data transformation and preparation. Rather, Power Pivot is more about loading data, creating data models and visualizing the data to gain important business insights. In Power Query, you can connect to many different data sources and manipulate the data instantly before loading it to any other platform.

Q.19. What do you know about the Power Map?

Ans. Power Map is also an Excel add-in whose outputs we can use in Power BI. Power Map has the tools to visualize geo-coded data in maps. We can create 3-D visuals with heat maps, bubbles, columns on Bing maps. We can also use time-stamped data points on the map.

Q.20. What are the different types of data refreshes in Power BI?

In Power BI, data of published reports can be refreshed in four ways; Package refresh, Model refresh, Tile refresh, and Visual container refresh.

  • Package refresh – The package refresh will refresh the report by updating the data from SharePoint Online or OneDrive. Package refreshing does not refresh data from the source but from its cloud location.
  • Model or Data refresh – This type of refreshing refreshes data in a report from its original data source. Model or data refresh is done within the Power BI Service with the help of gateway permission for on-premises data sources. This method uses Refresh now, or Schedule refresh options.
  • Tile refresh – Tile refresh refreshes the cache data of all the tiles on a dashboard. It occurs in every 15 minutes automatically. If it does not happen, we can select the Refresh dashboard tiles option from the ellipses(…) icon on the dashboard.
  • Visual container refresh – This refresh refreshes the cache of all the visuals containers of a Power BI report.

Wait a minute! Have you checked – Power BI Dashboard Tutorial

Q.21. What are the calculated columns in Power BI?

Ans. A calculated column is a query or DAX formula generated column in a data table. Each value in a calculated column is calculated while the code is computed by the system. The values are a result of the evaluated and calculated DAX formula. The values of a calculated column are not compressed and hence take up more space in memory than other data values.

Q.22. How can we use a gateway for Power BI?

Ans. You will be ready to use the data gateway in Power BI by following the four steps given below:

  1. Install a gateway in your system.
  2. Add users to the gateway who need to access data from on-premises data sources.
  3. Connect to on-premises data sources. This step is important because this is how users can use data from on-premises data sources for data analysis and report generation.
  4. Refresh on-premises data to keep the data in reports and dashboards up to date.

Q.23. What is an Advanced editor in Power BI?

Ans. An Advanced Editor is an editor within the Power Query Editor. It is used to view the M-code for any query. The Advanced Editor is generally used to check the syntax of a query that will be run in the editor.

We can access the Advanced Editor from the Home or View tab in Power Query Editor. We can select a query from the list of queries and work with it in the Advanced editor window and analyze the code step by step.

Q.24. In what ways can you shape or organize data in Power BI?

Ans. There are three common data shaping techniques in Power BI:

  • Removing the columns and rows from a data table.
  • Adding indexes for the data tables.
  • Applying a Sort Order on the values of a data table.

Q.25. How can we refresh Power BI reports after publishing them to the cloud?

Ans. We can refresh the Power BI reports after they are published on the cloud using gateways. If we want to refresh the data of a report published on SharePoint, then Data Management Gateway is used. And if the report is published on the Power BI website then Power BI Personal Gateway is used for data refreshing.

Q.26. Can we map geographic data into Power BI Reports? If yes, then how?

Ans. Yes, we can map the geographic data into Power BI reports in several ways. Power BI has integration with Bing Maps which provides the coordinates of locations on the map by default. This process is known as geocoding and the users don’t need to manually provide longitude and latitude coordinates. You can use the map charts and filled map charts in Power BI to use the geographic data in Power BI reports and dashboards.

Do you know – How to apply Filters in Power BI Reports

Q.27. What is z-order in Power BI?

Ans. The z-order in Power BI is referred to the strategy or order of arrangement of different elements on a Power BI report. The z-order helps us in properly arranging all the elements in a report such as visualizations, tables, slicers, etc. The Arrange option in Power BI helps us arrange or place report elements in z-order. Using that option, we can bring objects on front, send them to back, and layer the visualizations, according to our need. So, in a nutshell, z-order is the order of arranging or layering multiple objects and visual elements in a report on top of each other. Z-order is also applied when we are using shapes to enhance the design of a report.

Q.28. What are the important prerequisites for connecting Power BI to a database in Azure SQL Database?

Ans. In order to establish a secure connection of Power BI and Azure SQL Database, there is one important prerequisite. Before we make an attempt to connect to an Azure SQL Database, we should configure the firewall settings to allow remote connections. If firewall settings are not set up, we cannot have a stable and secure connection to Azure SQL Database.

Q.29. What are the prerequisites of installing a gateway in your system?

Ans. The following requirements are necessary for installing a gateway in our systems:

  • .NET 4.5 Framework
  • Windows 7 (64-bit version)/ Windows Server 2008 R2 or later
  • 8 core CPU
  • 8 GB memory
  • Windows 2012 R2 (64-bit)

Q.30. Can we use Power BI on mobile devices?

Ans. Yes, we can use Power BI on mobile devices. Power BI has mobile compatible apps for Android devices, iOS devices, and Windows 10 devices. We can download the Power BI app for a preferred device from app stores such as Google Play, Microsoft Store or Apple App Store.

Summary

This comes to the end of Power BI interview questions and answers for experienced professionals. After the first part of Power BI interview questions by DataFlair, these questions did not trouble you much. Or did they? If you have any kind of confusion in a particular concept of Power BI, check out the Power BI tutorial series and revise them thoroughly.

Also, if you have come across with any queries in the Power BI interview questions and answers, share them in the comment section. Our experts will assist you in the best possible way.

All the best for your interview!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.