Power BI Interview Questions

Power BI Interview Questions

Power BI architecture includes the following components:

• Power BI Desktop: Hundreds of data sources are collected in the desktop which generates report and visualizations.
• Online services: All the reports from desktop BI are published here. Power BI uses Software-as-Service(SaaS).
• Power BI Gateway: It works with On-premises information. It sends queries to the data source and returns the result to gateway cloud service. Power BI data gateway keeps the data fresh and updates with minimum effort.
• Mobile App: We can be connected anywhere with data. Power BI app works in the following Platform.

Older Components:

There are 5 different components of Power BI.

Power Pivot: Data transformation engine that fetches and cleans data, and loads on to 

Power Query: Data transformation engine that operated the loaded data.

Power Q&A: Makes it possible for users to interact with reports using simple English language.

Power View: This data visualization technology lets users create interactive charts, graphs, maps, and other visuals.

Power Map: Enables the processing of accurate geographic locations in data sets.

Types of filters used in Power BI reports.

• Visual Level Filters
• Page Level Filters
• Drill Through Filters
• Report Level Filters.

• Visual-level Filters: These filters work on only an individual visualization, reducing the amount of data that the visualization can see. Moreover, visual-level filters can filter both data and calculations.
• Page-level Filters: These filters work at the report-page level. Different pages in the same report can have different page-level filters.
• Report-level Filters: There filters work on the entire report, filtering all pages and visualizations included in the report.
• Drill through Filters: With drill-through in Power BI Desktop, users can create a page in their reports that focuses on specific entities such as suppliers, customers, or manufacturers.

Content Packs in Power BI.

• It is a collection of related documents or files that are stored as a group. In Power BI, there are two types of content packs, firstly those from services providers like Google Analytics, Marketo or Salesforce and secondly those created and shared by other users in your organization.

What is Power BI Q&A?

Power BI Q&A is a natural language querying tool which helps in finding answers to our questions from our data to get the results we need from it. We do this by typing into a dialog box on our Dashboard, the engine spontaneously produces an answer. Similar to Power View, Power BI Q&A interprets our questions and shows us a related query of what we are looking into our data.

For this to work, a dashboard should be created for your report. On the top of your dashboard, you will see Q&A question bar. To get into a table view you need to type the name given to the set of data. For example, you can type ‘sales’, ‘show sales’ and so on.

‘Where’ keyword can be used to filter values. For example, show sales where title “The Alchemist”. This will fetch all sales for the book “The Alchemist”

What is DAX?

DAX

DAX stands for Data Analysis Expression. It lets us do the basic calculation and data interpretation on our data in Power Pivot model. It is a functional language used to measure computed column and computed field.

What are many-to-many relationships and how can they be addressed n Power BI?

Many to Many relationships can be used in Power BI. The ‘crossfilter’ function can be used in Power Pivot for Excel. DAX can be used to check the metric and optionally alter the filter setting.

What are Power BI components?

Components of Power BI

The key components of Power BI are as follows,

• Power BI Service
• Power Query
• Power Pivot
• Power View
• Power Map
• Data Management Gateway
• Power BI Q&A
• Data Catalog

What are the different Excel BI add-in?

Ans: Below are the most important BI add-in to Excel:

• Power Query: It helps in finding, editing and loading external data.
• Power Pivot: Its mainly used for data modeling and analysis.

• Power View: It is used to design visual and interactively reports.
• Power Map: It helps to display insights on 3D Map.

• Which language is used in Power Query?

The programming language used in Power Query is called M-Code. It is simple to use and similar to other languages. M-code is case sensitive language.

What are benefits of using variables in DAX?

Variables can create more spontaneous DAX expressions which are more logical to interpret. Variables are only scoped to their query, they cannot be shared among queries or be defined at the model level. When we declare and evaluate a variable, the variables can be reused numerous times in a DAX expression, thus avoiding additional queries of the reference or source database.

What are the most common DAX Functions used?

Some of the most common DAX functions used are as follows,

• IF | AND | OR | SWITCH
• UNION | INTERSECT | EXCEPT
• GEOMEAN | MEDIAN | DATEDIFF
• VALUES | ALL | FILTER | CALCULATE
• ISBLANK | ISFILTERED | ISCROSSFILTERED
• SUM | MIN | MAX | AVG | COUNTROWS | DISTINCTCOUNT
• NATURALINNERJOIN | NATURALLEFTROUTERJOIN | VAR (Variables).

What is calculated column in Power BI?

The Data Analysis Expressions (i.e. DAX) which are calculated during the process of refreshing of a model for each row of the said column and can be used like any other column in the model is called calculated column. In case of large tables If the calculated column is not present in the source system, the calculated column can also reduce processing performance. They are not compressed and this consumes more memory and results in reduced query performance.

Ques.) What is the advantage of ‘Calculate’ and ‘Calculatable’ function? What are its limitations?

Advantage – Add to existing filter context of queries. Override filter context from queries. Remove existing filter context from queries.

Limitations – Filter parameters cannot reference a metric and can only operate on a single column at a time.

List some Power BI Desktop Visualizations.

Visualization Pane

Some of the visualizations that can be found on the visualization pane are as follows,

• Stacked Bar Chart | Stacked Column Chart | Clustered Bar Chart | Clustered Column Chart | Line Chart | Area Chart | Stacked Area Chart | Line and Stacked Column Chart | Line and Clustered Column Chart | Waterfall Chart | Scatter Chart | Pie Chart | Treemap | Table | Funnel | Gauge | Multi-Row Card | Card | KPI | Slicer | Doughnut Chart

What is Power Pivot?

Power Pivot helps us to analyse our data in order to make timely business decisions without requiring IT assistance. Power Pivot is a Microsoft Excel add-in that enables us to import very large scale of rows of data from multiple data sources into a single Excel workbook. It lets us create relationships between complex data, creates calculated columns and measures using formula. It lets us create Pivot Tables and Pivot Charts.

Ques.) What is Power BI Desktop?

Power BI Desktop is a free desktop application available to download for Windows operating system. Power BI Desktop works in sync with Power BI service by providing advanced data research, exploration, modelling. It enables us to create reports with highly interactive and beautiful looking visualizations. We can save our work to a file or publish our data reports right to the Power BI server in order to share it with other people throughout the world.

Ques.) What purpose is x-velocity in-memory analytics engine used in Power Pivot?

The main engine behind power pivot is the x-Velocity in-memory analytics engine. It can handle a large amount of data because it stores data in columnar databases and in-memory analytics. Thus resulting in faster processing of data as it loads all data to RAM memory.

Differentiate between Power BI and Tableau?

Criteria	Power BI	Tableau
Free Version	Yes	No
Use of natural language	Yes	No
Type of tool	Cloud-based BI to get full view of critical data	Intuitive BI tool for data discovery
Supported OS	Windows	Windows & Mac

Define Power BI

A cloud-based information sharing tool, Power BI enables anybody to examine and visualize information with more noteworthy productivity, speed and comprehension. Additionally, it helps in associating clients to an extensive variety of information with the assistance of simple to-operate dashboards and interactive reports which brings data to life

What are the parts of Power BI?

• Power BI – Power BI is a cloud-based business service. It enables the client to imagine and interpret information with higher rate, and execution.

• Excel  BI Tool Kit – In the core of Microsoft’s Power BI is the Excel BI Toolkit. This comprises of Excel and four add-ins that enable us to transfer, shape, create, and show our analyses. These components are as per the following,

• Power Query – It Easily investigates, access, and change public and internal information sources.
• Power Pivot – It Perform information modelling for in-memory analytics.
• Power View – It Analyzes and represents data as an interactive data visualization utilizing Power View.
• Power Map – It helps in Bringing information to existence with interactive  visualization.

Name some of the filters used in Power BI reports.

• Page Level Filters
• Visual Level Filters
• Drill Through Filters
• Report Level Filters.

What is Power BI Desktop?

Power BI Desktop works well with Power BI service by giving the most advanced data visualization, modeling,and report creation with profoundly interactive visualizations. You can spare your work to a document, and distribute your information and reports ideal to your Power BI site to impart to others.

What is your understanding on content packs?

Content packs for administrations are pre-developed solutions for popular administrations as a feature of the Power BI experience. An endorser of a supported service can rapidly interface with their account from Power BI and see their data through live dashboards and reports that have been pre-developed for them.

What are the types of Data Refresh in Power BI?

Mainly there are four different types of refresh that can happen in Power BI. They are:

• Package refresh
• Model or data refresh
• Tile refresh
• Visual container refresh

What are the different components of Power BI?

• Power BI Service
• Power Query
• Power Pivot
• Power View
• Power Map
• Data Management Gateway
• Power BI Q & A
• Data Catalog

What are benefits of using variables in DAX?

Variables can make more unconstrained DAX expressions which are more consistent to interpret.They can’t be shared among queries or be characterized at the model level. When it is declared,the variables can be reused for various circumstances in a DAX expression, in this way staying away from extra queries of the reference or source database.

What is Power Pivot?

Power Pivot helps in analyzing our data so as to settle on convenient business choices without needing IT support. It empowers us to import large scale of data from numerous data sources into a single Excel sheet. It gives us a chance to make connections between complex data. It helps us to create Pivot Tables and Pivot Charts.

What is DAX ?

In order to do data analysis on data and for calculation in power pivot, Data Analysis Expression (DAX) is used.

• Dax works on column values
• We can modify or insert data using DAX
• The calculated columns can  be calculated but the same cannot be done for rows using DAX

What is the default visual interaction in Power BI ?

The default visual interaction should be “Drill Mode On”, we can set this to on off, in the event that we keep off, we need to change the setting without fail. Rather set the Default to ‘Drill Mode On’.

Drilling Mode on, if the aggregates are at yearly level, the drill mode on can empower client to drill down to month to month, week by week, etc.

What is the Difference between Microsoft Business Intelligence tool - Power BI Desktop and Power BI?

Power BI Desktop

It is a Windows application (Client) running on the PC. It is a client machine which can be connected with various data sources from your PC to the data in the local PC or the information in the server.

Power BI

Power BI is a cloud solution. We can utilize internet browser to connect with Power BI. We can implement similar advancement work utilizing either Power BI OR Power BI Desktop.

What are the connectivity modes available in Power BI?

Mainly there are two types of connectivity modes

• Import
• DirectQuery

____________________________

Simple Report

So, when you start I gonna assume you have got some data, there is a get databutton on the top that allows you to connect to the server or local storage. After you pull the data you will see the set of data or fields you have pulled in. In the middle you have got a nice blank canvas, and this is the place where you can start building visualizations, doing your analysis, and you are given this visualization pane which gives you access to the gallery of visuals that it provides like box, custom visuals about which I will talk in a bit more later down the post. Below it, you can control the look of the visuals of your page. and building things is very simple, you find the fields you are interested in and drag them into the canvas, and it will start creating stuff for you, so if you place like something a measure like sales amount, it will give you a chart, so if I want to slice it by style of car, for example, we will get a little visualization showing that we have been selling lots of Diesel Cars, and lots of Sports Cars, very simple!

Canvas [Left], Visualization Pane [Middle], Fields or Dataset Pane [Right]

Visual Interaction

Let’s try some other ones. Let’s take the sales amount again and slice it using time and let us turn it into a line chart. so converting between different visualization is pretty simple, you just select the one you want from the visualization pane and then finally add something new, type of product, model, make, sales margin etc. and here is my report in just ten to twelve clicks. The report is showing really simple visualization that shows me some of the details of the products we have sold. The great thing about these visualizations is that they are all instantly interactive, I don’t have to do anything else, I can just click on these things and assuming data will come from the same place, things get cross filtered, highlighted, the spike adjusts as per the choice of the bar we select.

The other thing I can do here is start formatting and changing the way this report looks like adding titles is really easy and I can just choose to insert a text box. I can also do drawing like shapes and lines like if I want to put an arrow in to highlight a particular time period. I’ve got a bunch of controls that I can just start to embellish and add things to my report and of course I can go and start changing the format of these charts and visuals as well. so this visualization pane also contains under this formatting tab, all of the different options that you can find here to control whether or not we are showing the axis, what those axes look like, do we have a title for it, things like the colour for the data. Power BI provides all these formattings to give you more beautiful reports.

Simple BI Reporting – Car Sales; Spike and Bar Chart style visualization interaction

Conditional formatting with the same number of gradient colour

You can change the colour of your graph dynamically based on the value in your data. Let’s take a sample data from various gamers who play the game Halo or Destiny. So after pulling the data, we have got various fields of data like ‘number_of_kills’, ‘number_of_times_died’, ‘headshots’, etc.

Drag and drop the fields – ‘number_of_kills’ and ‘gamer_name’ on the canvas then select the bar chart from the visualization pane. We can see the name of the gamer and their respective kills represented by bars. The higher the bar the higher the kills.

To make BI Reporting powerful we can create conditional formatting by setting the gradient colour in the formatting tab. By doing this the intensity of the bar colour will change as per the number of kills, more he kills the stronger the colour. Lesser the kills, lower the colour intensity.

Now if we want a different colour to represent a different state, we have a workaround. We select the ‘max’ colour and ‘min’ colour and the gradient will be created by the in-between shades. We can set the value ‘1’ for each ‘max’, ‘medium’ and ‘min’, and select the ‘max’ colour as green and ‘min’ colour as red. So by doing this, you can see the gamer who has performed maximum kills, his bar will be highlighted as green and the gamer whose kills are the minimum will be represented by a red bar. Microsoft is still working on this feature and making it more user-friendly. You can send the feedback on this feature. More the feedback a feature receives, the more they concentrate on implementing and making that feature better.

Gamer Data – Total Kills; Colour Gradient

Gamer Data – Total Kills; Colour Formatting

Alignment

To make the reports neat we can multi-select all the visualization, go to Formattab at the top. Click on align and select the desired alignment to make everything line up. A simple feature but a great time saver and making our reports beautiful. So no more OCD regarding lining up the charts. 🙂

Power BI and Excel

Use Microsoft Excel and Power BI together and build powerful, consistent reports and visuals.

Get Excel data into Power BI

In this section, we’ll first take a look at how you can send an Excel workbook file containing a simple table from local storage into Power BI. You’ll then learn how you can begin viewing that table’s data in Power BI by building a report.

Data should be formatted as Table

In order for Power BI to get data from your workbook, that data needs to be formatted as a table. It’s simple. In Excel, you can highlight a range of cells, then go to the Insert tab of the Excel ribbon, then click Table.

Excel Database

Your files do not need to be on a local storage, of course. If you have uploaded your files to OneDrive, Google Drive, Box, or any other cloud storage, that’s even better. OneDrive being the best option since it is from Microsoft and thus provides the best integration.

Importing Data from Local Storage or Cloud

Importing…

After importing data into Power BI, you can begin building reports.

What formats is Power BI available in?

• Power BI desktop—for desktop computers
• Power BI service—an online SaaS (software as a service)
• Mobile Power BI apps—for iOS and Android devices

All of these can be used in conjunction. For example, you might create a report on your desktop, and then publish and share it online so that colleagues on mobile devices can read it. 

Describe the building blocks of Power BI.

• Visualizations. A visualization is a chart, graph or similar visual representation of data.
• Datasets. A dataset is the group of data used to create a visualization, such as a column of sales figures. Datasets can be combined and filtered from different sources using built-in connectors.
• Reports. A report is a group of visualizations on one or more pages; for example, charts, graphs, and maps can be combined to create a report. 
• Dashboards. A dashboard lets you share a one-page visualization with others, who can then interact with your dashboard.
• Tiles. A tile is a visualization on your dashboard or in your report. As the creator, you can move tiles around.

What are the main components of the Power BI toolkit, and what do they do?

• Power Query: lets you discover, access and consolidate info from different sources
• Power Pivot: a modeling tool
• Power View: a presentation tool for creating charts, tables and more
• Power Map: lets you create geospatial representations of your data
• Power Q&A: lets you use natural language to get answers to questions; for example, “What were the total sales last week?”

What is a content pack, and why would you use one?

A content pack is a ready-made, predefined collection of visualizations and reports using your chosen service (for example, Salesforce). You’d use one when you want to get up and running quickly, instead of creating a report from scratch.

Describe DAX.

DAX stands for Data Analysis Expressions. It’s a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have. 

What are the three fundamental concepts of DAX?

• Syntax. This is how the formula is written—that is, the elements that comprise it. The Syntax includes functions such as SUM (used when you want to add figures). If the syntax isn’t correct, you’ll get an error message.
• Functions. These are formulas that use specific values (also known as arguments) in a certain order to perform a calculation, similar to the functions in Excel. The categories of functions are date/time, time intelligence, information, logical, mathematical, statistical, text, parent/child and other.
• Context. There are two types: row context and filter context. Whenever a formula has a function that applies filters to identify a single row in a table, row context comes into play. When one or more filters are applied in a calculation that determines a result or value, filter context comes into play.

Why and how would you use a custom visual file?

You’d use a custom visual file if the prepackaged files don’t fit the needs of your business. Custom visual files are created by developers, and you can import them and use them in the same way as you would the pre-packaged files.

What are some of the most common sources for data in the Get Data menu?

Excel, Power BI datasets, web, text, SQL server and analysis services.

What are the categories of data types?

• All
• File
• Database
• Power BI
• Azure
• Online Services
• Other

Name some commonly used tasks in the Query Editor.

• Connect to data
• Shape and combine data
• Group rows
• Pivot columns
• Create custom columns
• Query formulas

What is grouping, and how would you use it?

Power BI Desktop groups the data in your visuals into chunks. You can, however, define your own groups and bins. For grouping, use Ctrl + click to select multiple elements in the visual. Right-click one of those elements and, from the menu that appears, choose Group. In the Groups window, you can create new groups or modify existing ones.

Describe responsive slicers.

On a report page, you can resize a responsive slicer to different sizes and shapes, and the data contained in it will be rearranged to match. If a visual becomes too small to be useful, an icon representing the visual takes its place, saving space on the report page.

In Power Query, what is query folding?

This is when steps defined in the Query Editor are translated into SQL and executed by the source database, instead of by your own device. It helps with scalability and efficient processing.

Explain the term “M language”.

This is the programming language used in Power Query. It’s a functional, case-sensitive language that’s similar to other programming languages and easy to use.

What are the differences between visual-level filters, page-level filters, and report-level filters? 

Visual-level filters filter data within a single visualization. Page-level filters work on an entire page in a report, and different pages can have different filters. Report-level filters filter all the visualizations and pages in the report.

How does the Schedule Refresh feature work?

You can configure an automatic refreshing of data daily or weekly, and at different times. You can schedule only one refresh maximum daily unless you have Power BI Pro. In the Schedule Refresh section, simply use the pulldown menu choices to select frequency, time zone and time of day.

What information is needed to create a map in Power Map?

Power Map can display visualizations that are geographical in nature. Therefore, some kind of location data is needed—for example, city, state, country or latitude and longitude.