PowerBI Interview Questions Set 04

61. What is the different Excel BI add-in?

Below is the most important BI add-in to Excel:

• Power Query: It helps in finding, editing and loading external data.
• Power Pivot: It’s 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.

62. What is Power Pivot?

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 Pivot Charts. You can then further analyse the data so that you can make timely business decisions without requiring IT assistance.

63. What is Power Pivot Data Model?

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.

64. What are some of differences in data modeling between Power BI Desktop and Power Pivot for Excel?

Here are some of the differences:

• Power BI Desktop supports bi-directional cross filtering relationships, security, calculated tables, and Direct Query options.
• Power Pivot for Excel has single direction (one to many) relationships, calculated columns only, and supports import mode only. Security roles cannot be defined in Power Pivot for Excel.

65. Can we have more than one active relationship between two tables in data model of Power Pivot?

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 is active.

66. What is Power Query?

Power query is an ETL Tool used to shape, clean and transform data using intuitive interfaces without having to use coding. It helps the user to:

• Import Data from wide range of sources from files, databases, big data, social media data, etc.
• Join and append data from multiple data sources.
• Shape data as per requirement by removing and adding data.

67. What are the data destinations for Power Queries?

There are two destinations for output we get from Power Query:

1. Load to a table in a worksheet.
2. Load to the Excel Data Model.

68. What is query folding in Power Query?

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.

69. What are query parameters and Power BI templates?

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.

• The parameter selection can then be used by the query and calculations.
• PBIX files can be exported as Templates (PBIT files).
• Templates contain everything in the PBIX except the data itself.

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.

70. Which language is used in Power Query?

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.

71. Why do we need Power Query when Power Pivot can import data from mostly used sources?

Power Query is a self-service ETL (Extract, Transform, and 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.

72. What is Power Map?

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 up to 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.

73. What is the primary requirement for a table to be used in Power Map?

For a data to be consumed in Power Map there should be location data like:

• Latitude/Longitude pair
• Street, City, Country/Region, Zip Code/Postal Code, and State/Province, which can be geolocated by Bing

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.

74. What is Power View?

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:

• Charts
• Line charts
• Pie charts
• Maps
• Tiles
• Cards
• Images
• Tables
• Power View
• Multiples Visualizations
• Bubble and scatter charts
• Key performance indicators (KPIs)

75. What is Power BI Designer?

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.

76. Can we refresh our Power BI reports once uploaded to cloud (Share point or Powebi.com)?

Yes we can refresh our reports through Data Management gateway (for SharePoint), and Power BI Personal gateway (for Powerbi.com)

77. Is Power BI available on-premises?

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 schedule 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.

78. What is data management gateway and Power BI personal gateway?

Gateway acts a bridge between on-premises data sources and Azure cloud services.

• Personal Gateway:

1. Import Only, Power BI Service Only, No central monitoring/managing.
2. Can only be used by one person (personal); can’t allow others to use this gateway.

• On-Premises Gateway:

1. Import and Direct Query supported.
2. Multiple users of the gateway for developing content.
3. Central monitoring and control.

79. What is Power BI Q&A?

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.

80. What are some ways that Excel experience can be leveraged with Power BI?

Below are some of the ways through which we can leverage Power BI:

• The Power BI Publisher for Excel:
• Can be used to pin Excel items (charts, ranges, pivot tables) to Power BI Service.
• Can be used to connect to datasets and reports stored in Power BI Service.
• Excel workbooks can be uploaded to Power BI and viewed in the browser like Excel Services.
• Excel reports in the Power BI service can be shared via Content Packs like other reports.
• Excel workbooks (model and tables) can be exported to service for PBI report creation.
• Excel workbook Power Pivot models can be imported to Power BI Desktop models.