Power BI

Test Questions

1. What are the connectivity modes we have in Power bi?

 Import Mode
 Direct Query Mode
 Live Connection Mode
 Composite Model
 Direct Lake Mode

2. Dimensions and Measures in Power Bi?

 Dimensions are descriptive attributes (e.g., Product Name, Date,
Customer) that provide context for Measures, which are calculated
numerical values (e.g., Total Sales, Average Price, Count of Orders)
used for analysis and aggregation in Power BI.

3. Data types in Power BI?

1.Number
Whole Number - 10 20
Decimal Number - 10.25 0.89
Currency Data types -$200
Percentage Data types - 85.26%
2.Date
Date - 03/21/2023
Date and Time - 03/21/2023 12:00AM
Time -12:00 : 24:00
Durations : Periodic intravels
3.Text/String
Character and Name & Non Numberic
4.Boolean
True or False
5.Binary
0101

4. File Format Power BI?

Primary Power BI Formats
 .pbix: Standard Power BI Desktop file (contains data, model, and visuals)
 .pbit: Template file (structure and queries, no data)
 .pbids: Data source connection information
 .pbip: Power BI Project file (for better source control)
 .pbir: Enhanced report format for PBIP (preview)
Common Import Formats
 .xlsx/.xlsm: Excel files
 .csv: Comma-separated values
 .pdf: For table extraction

5. Data and Meta Data?

Data in Power BI
  Imported from various sources
 Stored in tables within the data model
 Used for analysis and visualization
Metadata in Power BI
 Automatically generated for tables and columns
 Includes information like names, data types, and relationships
 Can be customized (e.g., renaming, creating hierarchies)
 Uses an enhanced semantic model format
 Supports automatic upgrades for compatibility
 Allows for granular refresh control
 It can be extended with custom metadata using DAX or additional tables
 No built-in centralized metadata repository

6. What is Query Setting?

The Query Settings in Power BI refers to the pane on the right side of the
Power Query Editor that displays all the steps and transformations applied to a
particular query. Here are the key points about Query Settings:
 It shows a list of all applied steps for the selected query in chronological
order.
 Each step represents a specific data transformation or manipulation
performed on the data.
 Users can rename, delete, or reorder these steps as needed.
 The Applied Steps section provides a clear record of the data transformation
process.
 All query steps are executed in the order they appear in the Applied Steps
window.
 It allows users to track and manage the data preparation process without
altering the original data source.
The Query Settings pane helps users easily understand and modify the data
transformation workflow.
7. Views in Power BI?
In short, views in Power BI offer several key benefits:
 Data transformation at the source
 An abstraction layer between database and reports
 Simplified data model in Power BI
 Enhanced security and access control
 Support for query folding
 Easier maintenance across multiple reports
Views can improve performance, flexibility, and maintainability of Power BI
solutions, especially when dealing with complex data structures or frequent
changes to underlying data sources.

8. Components of Power BI?

Here's a concise summary of the key components of Power BI:
 Power BI Desktop: Main tool for creating reports and data models
 Power Query: Data extraction and transformation tool
 Power Pivot: Data modeling and analysis engine
 Power View: Data visualization component
 Power BI Service: Cloud-based platform for sharing and collaboration
  Power BI Mobile Apps: For accessing reports on mobile devices
 Power BI Report Server: On-premises reporting solution
 Datasets: Collections of imported or connected data
 Reports: Visual representations of data
 Dashboards: Overview of key metrics and visualizations
 Data Sources: Various supported data inputs

9. PBIX vs PBIT?

Feature PBIX PBIT

Full name Power BI Experience Power BI Template
Data Contains full dataset Does not store data
storage
File size Larger Smaller
Purpose Complete the Power BI Template or blueprint
report
Structure Includes Data Model File Contains Data Model Schema File
Usage Sharing complete reports Creating standardized layouts or sharing query
with data logic without data
Conversion Final format Converts to PBIX when opened
Data May contain sensitive data Does not expose sensitive data
sensitivity
Typical use Distributing finished Sharing report structure or standardizing
case reports organizational reporting

10.Difference b/w Split and Extract in Power BI?

Feature Split Extract
Purpose Divides a column into multiple new Modifies content within the existing
columns column
Result Creates new columns Keeps original column structure
Use case Separating data into distinct columns Keeping only a portion of the original
data
Location in Power Transform menu Transform menu
Query
Input example "Database1 Detailed vendor "Database1 Detailed vendor
information available" information available"
Output example Two columns: Single column:
1. "Database1" "Database1"
2. "Detailed vendor information
available"
Based on Delimiter or number of characters Position, delimiter, or pattern
Flexibility High - can create multiple columns Limited to modifying existing column
or rows
Best for Structured data that needs to be Extracting specific parts of text
separated
Data structure Changes table structure Maintains original table structure
impact

11.Difference b/w Transpose and Pivot?

Featu Transpose Pivot
re
Purpose Rotates entire dataset Restructures and summarizes data
 Operatio Converts rows to columns and vice Converts unique values into columns
n versa
Data Maintains original data structure Creates new data structure
shape
Aggregati No aggregation Can perform aggregations (sum,
on count, etc.)
Use case Simple reorientation of data Data analysis and summarization
Result Same data, different orientation Condensed view of data

12. What do you mean by Pivot and Unpivot?

Pivot and Unpivot are data transformation operations that allow you to
restructure tabular data: Pivot:
 Rotates data from rows into columns
 Turns unique values from one column into multiple columns
 Typically performs aggregations on remaining values
 Useful for creating summary tables and cross-tabulations
Unpivot:
 Rotates data from columns into rows
 Converts multiple columns into two columns: a name column and a value
column
 Opposite operation of Pivot
 Useful for normalizing data into a more analytical format

13.Difference b/w Addcolumn and Transfer in Power BI?

Transform: Modifies existing columns in your dataset. It's used to change,
clean, or manipulate data within existing columns.
Add Column: This creates new columns in your dataset, increasing its size by
adding additional data.
14.Fill up and Fill down in Power BI?
Fill Down
o Replaces null values with the last non-empty value above
o Moves from top to bottom of a column
o Useful for data with merged cells or non-repeated headers
Fill Up
o Replaces null values with the first non-empty value below
o Moves from bottom to top of a column

15.What is Data Modelling? What are the methods we have in Power BI?
Data modeling in Power BI is the process of structuring and organizing data to
create efficient and meaningful relationships between different data sources. The
main methods for data modeling in Power BI are:
 Conceptual data modeling: Defining the high-level structure and
relationships between data entities.
 Logical data modeling: Creating a more detailed model that specifies
attributes, relationships, and data types.
 Physical data modeling: Implementing the actual data structure in Power
BI, including tables, columns, and relationships.
16.What do you mean by Merge and Append Query in Power BI?

Feature Merge Query Append Query

Operation Combines tables horizontally Combines tables vertically

Similar to JOIN UNION

SQL
Primary use Add new columns from one table Add new rows from one table to
to another another
Key Common key between tables Matching column structures
requirement
Result More columns, same number of More rows, same number of columns
rows
Best used Bringing in additional attributes Combining similar data from multiple
when from related tables tables

Data Relates data between tables Stacks similar data

relationship

Example Adding product details to sales Combining monthly sales reports

scenario data into one annual report

 Merge adds columns (horizontally) based on matching keys

 Append adds rows (vertically) assuming matching column structures

17.When can we use Merge and Append Query in Power BI?

Use Merge when you need to add columns based on related data, and use
Append when you need to combine similar data structures by adding rows. The
choice depends on whether you want to expand your data horizontally (Merge) or
vertically (Append)

18.What do you mean by relationship? How does it differ from merge? and its
type?
Relationships vs. Merges in Power BI
Aspect Relationships Merges
Definition Create connections between Physically combine tables in
tables in the data model Power Query
Data Tables remain separate Results in a single consolidated
Structure table
When to Use Large datasets, complex data Smaller datasets, need to
models combine data before analysis
Types • One-to-many • Inner join
• Many-to-one • Left outer
• One-to-one • Right outer
• Many-to-many • Full outer
• Left anti
• Right anti
Scalability More scalable for large datasets Can increase file size and refresh
time for large datasets
Flexibility More flexible for complex Less flexible, but can be more
models efficient for small datasets
Data Model Maintains original table Changes table structure in the
Impact structure data model

19.How many active relationships we can give with the table?

20.What is meant by cardinality?

refers to the number of unique elements in a set or the number of distinct
values in a database column. It's a measure of the "uniqueness" of data. In
database design and data modeling, cardinality also describes the numerical
relationship between two related tables or entities.Key points:
 In set theory: It's the number of elements in a set.
 In databases: It's the number of distinct values in a column.
 In relationships: It describes how many instances of one entity relate to
instances of another entity (e.g., one-to-one, one-to-many, many-to-
many).

21. Give me the all joins for output below the table
TABLE 1 TABLE 2
1 1
1 1
2 1
2 2
3 2
4 5

Inner Join
Column 1 Column 1
(Table 1) (Table 2)
1 1
1 1
2 2

2 2

Left Outer Join

Column 1 Column 1
(Table 1) (Table 2)
1 1
1 1
2 2
2 2
3 null
4 null
Right Outer Join
Column 1 Column 1
 (Table 1) (Table 2)
1 1
1 1
2 2
2 2
null 5

Full Outer Join

Column 1 Column 1
(Table 1) (Table 2)
1 1
1 1
2 2
2 2
3 null
4 null
null 5

22.Filter Context vs Row Context?

Filter Context:
 A set of filters applied to the entire data model before evaluating a DAX
expression
 Affects all rows in tables across the data model
 Automatically propagates through relationships
 Can be modified using functions like CALCULATE
 Applied by slicers, filters, and other visual elements in reports
Row Context:
 Refers to the current row when evaluating an expression
 Only considers values within the same row
 Does not automatically propagate through relationships
 Typically used in calculated columns and iterative functions like SUMX

23.New Column vs New Table vs New Measure use case with example?

Featur Use Case Example Code Example

e
New For row-by-row Calculating profit Profit Margin = Sales[Price] -
Column calculations that margin for each Sales[Cost]
don't change with product sale
user interaction
New For dynamic Calculating total Total Sales = SUM(Sales[Amount])
Measur calculations that sales for the
e respond to filters selected time
and slicers period
New When creating Creating a date Date = CALENDAR(DATE(2020, 1,
Table entirely new table for time 1), DATE(2025, 12, 31))
datasets within intelligence
your model functions

24.All Data Functions with example?

 Function Description Example
SUM Adds up all Total Sales = SUM(Sales[Amount])
values in a
column
AVERAGE Calculates Avg Price = AVERAGE(Products[Price])
the
arithmetic
mean of a
column
COUNT Counts non- Customer Count =
blank values COUNT(Customers[CustomerID])
in a column
DISTINCTCOUNT Counts Unique Products =
unique DISTINCTCOUNT(Sales[ProductID])
values in a
column
MAX Returns the Highest Sale = MAX(Sales[Amount])
largest value
in a column
MIN Returns the Lowest Price = MIN(Products[Price])
smallest
value in a
column
FILTER Filters a High Value Sales =
table based CALCULATE(SUM(Sales[Amount]),
on specified FILTER(Sales, Sales[Amount] > 1000))
conditions
RELATED Retrieves a Product Category =
value from a RELATED(Products[Category])
related table
CALCULATE Modifies the Sales Last Year =
filter context CALCULATE(SUM(Sales[Amount]),
for a SAMEPERIODLASTYEAR(Calendar[Date]))
calculation
DATEADD Shifts dates Sales Next Month =
by a CALCULATE(SUM(Sales[Amount]),
specified DATEADD(Calendar[Date], 1, MONTH))
interval
RANKX Ranks values Sales Rank = RANKX(ALL(Products), [Total
within a Sales])
specified
context
TOPN Returns the Top 5 Products = TOPN(5, Products, [Total
top N rows of Sales], DESC)
a table

25.What is the difference b/w calendar and calendar auto?

Feature Calendar CalendarAuto

Date User-specified Automatically determined
Range
Syntax CALENDAR(start_date, CALENDARAUTO([fiscal_year_end_mont
end_date) h])
Control More control over exact Adapts to data in the model
 date range
Flexibility Fixed dates provided by Dynamic, adjusts to data changes
user
Use Case Specific custom date Automatic coverage of all dates in
ranges model
Output Table with single "Date" Table with single "Date" column
column
Adaptabilit Static, requires manual Automatically updates with data
y updates changes
Ease of Requires knowledge of Simpler, requires less input
Use date range
Performan May be faster for small, May have slight overhead for scanning
ce specific ranges data

26.Difference b/w calculate and calculate table?

Aspect CALCULATE CALCULATETABLE
Return Scalar value Table
Type
Input Expression returning Expression returning a table
a single value
Usage Modifies filter Modifies filter context for table
context for scalar expressions
calculations
Typical Aggregations, single Virtual tables, complex filters
Use value metrics
Cases
Syntax CALCULATE(expressi CALCULATETABLE(table_expres
on, filter1, filter2, ...) sion, filter1, filter2, ...)
Context Changes context for Changes context for entire
Modificati scalar result table
on
Performa Generally faster for Can be slower due to table
nce single values manipulation
Nesting Often used within Commonly used in other DAX
measures functions

27.Difference b/w group by and summarise function?

Feature GROUPBY SUMMARIZE

Best for Local columns, complex Existing model columns
aggregations
Performan Better with local columns Better with model columns
ce
Behavior More predictable results May have unexpected results due
to clustering
Memory Higher (materializes entire Lower (doesn't always materialize
use table) entire table)
Flexibility Excels with DAX-created Excels with existing table columns
columns
28.What are the Filter functions we have explain?
FILTER: Returns a subset of a table or expression based on specified
conditions.
ALL: Removes all filters from a table or column, returning all rows or values.
ALLEXCEPT: Removes all filters except those applied to specified columns.
ALLSELECTED: Removes context filters from columns and rows in the
current query while retaining other filters

29.What is the use of related functions?

The RELATED function in Power BI allows you to:
1. Access data from connected tables
2. Use information from one table in another
3. Create calculations using data from multiple tables
4. Simplify your data model by reducing the need for manual joins
5. Enable more complex analyses across your entire dataset

30.What is the use of variable functions?

variables in Power BI DAX formulas are used to:
1. Simplify complex calculations
2. Improve formula readability
3. Boost performance by calculating expressions once
4. Make debugging easier
5. Store and reuse intermediate results
6. Hold both single values and entire tables
7. Facilitate conditional logic

31.What is meant by Time Intel functions?

Time Intelligence functions in Power BI are special tools that help you work
with dates and time periods easily. They let you:
1. Compare data across different time periods (like this month vs. last
month)
2. Calculate totals for specific timeframes (like year-to-date sales)
3. Analyze trends over time
These functions make it much simpler to create time-based reports and
dashboards without complex calculations. They're especially useful for
business reporting where you often need to look at data by day, month,
quarter, or year.

32.Difference b/w DateYTD and TotalYTD?

The main differences between DATESYTD and TOTALYTD in Power BI DAX are:
1. Function purpose:
 DATESYTD returns a set of dates from the start of the year to the
last date in the current filter context.
 TOTALYTD evaluates an expression over the interval from the first
day of the year to the last date in the specified date column.
2. Syntax and usage:
 DATESYTD syntax: DATESYTD(dates[, year_end_date])
 TOTALYTD syntax: TOTALYTD(expression, dates[, filter])
 3. Calculation power:
 DATESYTD only provides a set of dates.
 TOTALYTD calculates an expression over the year-to-date period,
offering more flexibility.
4. Implementation:
 DATESYTD is often used in combination with CALCULATE to perform
calculations.
 TOTALYTD incorporates the calculation within the function itself.
5. Flexibility:
 TOTALYTD is more versatile as it can return year-to-date calculations
for any expression, not just totals.
In practice, TOTALYTD is often preferred for its built-in calculation capabilities,
while DATESYTD is useful when you need more control over the specific date
range or when working with custom calendar tables

33.Difference b/w parallel period and sameperiod last year?

Feature PARALLELPERIOD SAMEPERIODLASTYEAR

Time range Can go multiple intervals Only goes exactly one year back
back or forward
Interval Works with month, quarter, Only works with years
options and year intervals
Behavior Returns results based on Adapts dynamically to current
with specified interval level of date hierarchy
hierarchies
Syntax PARALLELPERIOD(dates, SAMEPERIODLASTYEAR(dates)
number_of_intervals, interval)
Use cases More flexible for comparing Simpler for year-over-year
arbitrary time periods comparisons
Flexibility Higher Lower
Ease of use More complex Simpler

34.What is meant by YoY/QoQ and its formula?

METRI MEANING FORMULA

C
YOY Year-over-Year (Current Year Value / Previous Year Value) - 1
QOQ Quarter-over- (Current Quarter Value / Previous Quarter
Quarter Value) – 1