Power BI - PL 300 - Pt3 Flashcards
What type of table stores details about business entities?
A. Fact table
B. Dimension table
C. Date table
D. Data table
B. Dimension table
A table that stores details about business entities is a Dimension table. A dimension table contains descriptive information about the data in the fact table
How many cardinalities do we have in Power BI? Name them
4:
- One to one
- One to many (best option)
- Many to one (best option)
- Many to Many
What is the by default cross filter direction?
Single-direction, where filters move from “one” side of a relationship to many.
What is a fact table? And
What is the easiest way to identify a fact table?
It carries values, metric information
Find a table with multiple metrics, and value fields, data size is big, It will contain all foreign keys. Not any primary keys.
On what basis do we merge two tables?
On the basis of a primary key
What needs to be the same in order to append data?
Column headers must match between data sets
What is a dimension table?
A table that provides descriptive context for the data in fact tables. It contains attributes such as product names, customer demographics, or time periods.
How can active and inactive relationships be identified between tables?
The inactive relationship between two tables is denoted by a dotted line connecting the two tables. Active relationships are denoted by a filled line.
You have a Power BI model that contains the following two tables:
Sales(Sales_ID, sales_date, sales_amount, ProductID)
Product(ProductID, Product_name)
There is a relationship between Sales and Product. You need to create a measure to calculate the average sales amount per product. Which of the following options are correct?
A. AVERAGE(Sales[Sales_amount])
B. AVERAGEX(VALUES(Product[Product_name]), [Total Sales])
C. AVERAGEX(ALL(Product), [Total Sales])
D. AVERAGEX(Product, [Total Sales])
AVERAGEX(VALUES(Product[Product_name]), [Total Sales]).
The VALUES function returns a one-column table that contains the distinct values from the Product_name column in the Product table. The AVERAGEX function then iterates over this table and calculates the average of the Total Sales measure for each product. [Total Sales] would be a measure you define as SUM(Sales[Sales_amount]). This represents the total sales amount for each product.
You have a Power BI model that contains the following two tables:
- Sales (Sales_ID, DateID, sales_amount)
- Date(DateID, Date, Month, Week, Year)
The tables have a relationship. You need to create a measure to calculate the sales for same period from the previous year. Which DAX formula should you use?
A. SUM(sales[sales_amount]) - CALCULATE(SUM(sales[sales_amount]), DATESYID(‘Date’[Date]))
B. CALCULATE(SUM(sales[sales_amount]), SAMEPERIODLASTYEAR(‘Date’[Date]))
C. SUM(sales[sales_amount]) “” CALCULATE(SUM(sales [sales_amount]), SAMEPERIODLASTYEAR(‘Date’[Date]))
D. CALCULATEx(SUM(sales(sales_amount]), DATESYID(‘Date’[Date]))
Answer B
CALCULATE(SUM(sales[sales_amount]), SAMEPERIODLASTYEAR (‘Date’[Date]))
AMEPERIODLASTYEAR function returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column
What is the difference between a fact table and a dimension table?
A. Fact tables store observations or events while dimension tables contain information about specific entities within the data
B. Fact tables contain information about specific entities while dimension tables contain information about observational data
C. Dimension tables tell you about specific roles in Power BI while fact tables tell you information about facts that are associated with those roles in Power BI
D. There is no difference
A. Fact tables store observations or events while dimension tables contain information about specific entities within the data
Which of the following relationships is the default form of directionality in Power BI
A. One to One (1:1) relationship
B. One to Many (1 : *) relationship
C. Power BI does not have a default directionality
D. Many to Many ( * : *) relationship
One to Many (1 : *) relationship
What does Year to Date (YTD) mean in TOTALYTD?
A. All the dates from exactly 12 months ago until the last date in the date filter
B. All the dates from 1st January in the filter year until the last date in the date filter
C. All the dates from today until 12 months into the future
D. All the dates from the first date in the date filter to the end of the year
B. All the dates from 1st January in the filter year until the last date in the date filter
Year to Date (YTD) in TOTALYTD means evaluating the year-to-date value of the expression in the current context.
When would you need to access the Data Source Settings?
A. If you need to connect to a new data source
B If you need to edit an existing query
C. If the file name or location changes
D. All of the above
D. All of the above
You would need to access the Data Source Settings in Power BI if you need to connect to a new data source, edit an existing query, or if the file name or location changes.
Which data profiling tool can you use to check the number of errors in a column?
A. Column quality
B. Column distribution
C. Column profile
D. Column quality & column profile
D. Column quality & column profile
Column quality checks the quality of the data in terms of valid, error, and empty values
Column distribution shows the distinct and unique records of the values in each of the columns as in a histogram visualLinks to an external site.
Colum profile provides an in-depth look at the data in a column including the number of error, empty, distinct and unique values.
Hence, both Column Profile and Column Quality can be used to check the number of errors in a column.
You work as an analyst at Cat Slacks and you’ve just been handed a csv file with yearly sales by department. After connecting to it in Power BI, you notice that each year has its own column. Which Power Query tool can you use to turn the multiple “Year” columns into rows?
A. Pivot
B. Unpivot
C. Transpose
D. Group By
B. Unpivot
Unpivot is used to rotate a column into rows. Pivot is used to rotate rows into columns, Transpose is used to rotate the entire table, and Group By is used to group rows based on the values in one or more columns
Which of the following sources lets users connect to a set of pre-wired connections?
A. SQL Database
B. Google Analytics
C. R scripts
D. All of the above
D. All of the above
Power BI can connect to a wide variety of data sources, including SQL Database, Google Analytics, and R scripts. You can use the “Get Data” function in Power BI to connect to these sources and import data for analysis and visualization.
In which storage mode are tables solely stored in-memory and queries fulfilled by cached data?
A. Import
B. DirectQuery
C. Dual
D. Native
A. Import
In Power BI’s Import mode, all your data is cached in-memory. This means that when you interact with your reports, Power BI serves queries from this fast, locally stored data, rather than going back to the external data source every time.
You are creating Power BI reports for your organisation that will be accessed by the sales team on their mobile devices. Which data location is not suitable if the source is a frequently updated flat file in CSV format?
A. Local
B. OneDrive for Business
C. OneDrive for Personal
D. Azure SQL Server
A. Local
If the source is frequently updated and is a flat file, the changes made to the file will not be reflected in the reports, unless the data source is manually updated in Power BI every time. Hence this data location is not suitable
You have the schema for the tables used in a bicycle franchise. In your role as a BI consultant, you have been asked to generate some insights from the data. There is a product table in the dataset.
The product table’s color column has abbreviations as well as names. For instance, there is both Black and BLK. What do you need to do to ensure consistency in the color column?
A. Export the table to Excel
B. Use Replace Values in the Transform ribbon in Power Query Editor
C. Edit the source applied step in M code
D. Remove duplicates in Power Query Editor
B. Use Replace Values in the Transform ribbon in Power Query Editor
You should use replace values in Power Query Editor to ensure the color names are consistent. For instance you would replace ‘BLK’ with ‘Black’.
You have the schema for the tables used in a bicycle franchise. In your role as a BI consultant, you have been asked to generate some insights from the data. There is a customer table in the dataset with a postcode column formatted as a whole number.
When reviewing the customer table, you find that some of the postcodes appear to be missing. For instance ‘0800’ does not seem to exist.
What do you need to do to ensure all the Postcodes from the original data are in your model?
A. Use ‘add columns from examples’ to fix discrepancies
B. Use ‘Transform’ and then adjust the rounding
C. Go to the Transform ribbon and click on ‘Use First Row as Header’
D. Make sure the Postcode data type is ‘text’ and not ‘whole number’
D. Make sure the Postcode data type is ‘text’ and not ‘whole number’
If the original data uses ‘0’ as a prefix for some post codes, converting to a whole number will remove the zero. For instance ‘0800’ becomes 800. To prevent losing this data, change the postcode data type to ‘text’ in the applied step ‘Changed Type.’
Which of these operations adds rows to an existing table?
A. Group By
B. Pivot
C. Merging Queries
D. Appending Queries
D. Appending Queries
Append Queries is used to add rows from one or more tables to an existing table. Group By is used to group rows based on the values in one or more columns, Pivot is used to rotate rows into columns, and Merge Queries is used to combine rows from two or more tables based on a related column between them. So, these options do not add rows to an existing table.
Which of the following sources lets you connect your data to other business applications?
A. Microsoft Dataverse
B. Microsoft Dataplatform
C. Microsoft Excel
D. Microsoft Dataflows
A. Microsoft Dataverse
Microsoft Dataverse allows you to connect directly to your data using Power BI Desktop to create reports and publish them to Power BI.
Microsoft Dataplatform Links to an external site.is not a valid option
Microsoft Dataflows is used to unify data from disparate sources and prepare it for consumption.
Which of the following sources lets users connect to a set of pre-wired connections?
A. PBIDS
B. JSON Files
C. SSAS Tabular
D. Dataflows
A. PBIDS
The correct option is PBIDS Files. PBIDS (Power BI Data Source) files let users connect to a set of pre-wired connections
Which of the following sources is used to unify data from disparate sources and prepare it for consumption?
A. PBIDS
B. JSON Files
C. SSAS Tabular
D. Dataflows
D. Dataflows
When should you use DirectQuery?
A. The source data never changes
B. Company policy states no data source restrictions
C. Dataset is too large to be stored in-memory
D. All of the above
C. Dataset is too large to be stored in-memory
DirectQuery is used when the dataset is too large to be stored in-memory. In DirectQuery mode, data is retrieved from the data source in real-time, rather than being imported and cached in Power BI.
You connect to an Excel file located on the desktop of your local computer. You, however decide to move the file from your desktop to your My Documents.
What do you have to do to in Power Query to prevent breaking the reporting model?
A. Nothing, Power BI maintains a permanent link to the file on the local computer
B. Click on the Navigation step in the Applied Steps and change the File path
C. Click on the Data Source settings in the Home tab and change the source
D. Create a shortcut on the Desktop with the same name as the source file that points to the file’s new location
C. Click on the Data Source settings in the Home tab and change the source
the connection is based on the file path. If you move the file to a new location (e.g., from the Desktop to My Documents), the original file path becomes invalid, breaking the connection.
B is Incorrect because: The Navigation step controls the selection of sheets/tables inside the file, not the file path itself.
What is the maximum number of rows that Power BI can load from a single CSV file by default?
A. 50,000
B. 100,000
C. 200,000
D. 10,000
C. 200,000
By default, Power BI can load up to 200,000 rows from a single CSV file. This limit can be adjusted in the options
Which two blocks make up the M code that runs your query?
A. do & while
B. if & then
C. for & each
D. let & in
D. let & in
The M code that runs your query in Power BI is made up of two blocks: let and in. The let block contains the steps of your query, while the in block specifies the final result of your query. The other options, do & while, if & then, and for & each, are not correct because they are not the two blocks that make up the M code that runs your query in Power BI.
Your dataset contains a “Gender” column with values “M” and “F.” In Power BI, how can you replace these values with “Male” and “Female” for better readability?
A. Split Columns
B. Replace Values
C. Conditional Split
D. Remove Columns
B. Replace Values
The Replace Values option replaces specified values in a column with new values.
In Power BI’s “Edit Queries” window, what technique can you use to remove unwanted rows based on specific criteria from your dataset?
A. Conditional Formatting
B. Group By
C. Filter Rows
D. Sort Ascending
C. Filter Rows
The Filter Rows option allows you to filter rows based on specific criteria, removing unwanted rows from your dataset.
What advantage does the Excel (.xlsx) format have over CSV when importing data into Power BI?
A. Support for multiple worksheets
B. Smaller file size
C. Faster data import
D. Greater compatibility with web sources
A. Support for multiple worksheets
Excel files can contain multiple worksheets, allowing you to organize and import data from multiple sources in a single file.
What is the purpose of the “Advanced Editor” option in Power BI’s Query Editor?
A. To manually edit the M code for data transformation
B. To create complex visualizations
C. To schedule data refresh
D. To share reports with other users
A. To manually edit the M code for data transformation
The Advanced Editor allows you to view and edit the M code that defines the data transformation steps in your query.
When connecting to an online data source in Power BI, what authentication method should you use to ensure secure data access?
A. Anonymous access
B. Basic Authentication
C. Windows Authentication
D. No Authentication
C. Windows Authentication
Windows Authentication uses the security features of the Windows operating system to authenticate users and provide secure data access.
What turns a collection of independent tables into a data model?
A. Connecting the tables via relationships, based on their common fields
B. Connecting to the tables in a single Power BI file
C. Merging the tables into a single “master” table
D. Giving the tables related names
A. Connecting the tables via relationships, based on their common fields
A collection of independent tables turns into a data model when the tables are connected via relationships, based on their common fields.
In a one-to-many relationship cardinality, what is the “many” attached to?
A. Primary Key
B. Foreign Key
C. Index Column
D. Lookup Table
B. Foreign Key
In a one-to-many relationship cardinality, the “many” is attached to the Foreign Key.
Which of these functions can be used to activate inactive relationships?
A. RELATED
B. RELATEDTABLE
C. USERELATIONSHIP
D. ACTIVATE
C. USERELATIONSHIP
Why the other answers are wrong:
The RELATED function returns a related value from another table, but it does not activate inactive relationships.
The RELATEDTABLE function returns a related table, but it does not activate inactive relationships.
There is no ACTIVATE function in Power BI.
Which of the following functions removes filter context?
A. ALL
B. CALCULATE
C. FILTER
D. ALL, CALCULATE and FILTER
A. ALL
You have a table named Sales that contains the columns Sales_ID, Sales_Date, Sales_Amount, and CustomerID. You need to create a measure that calculates the total sales for each quarter. Which DAX formula should you use?
A. TOTALQ(Sales[Sales_Amount], Sales[Sales_Date])
B. TOTALQTD(Sales[Sales_Amount], Sales[Sales_Date])
C. SUMX(Sales, Sales[Sales_Amount])
D. SUM(Sales[Sales_Amount])
B. TOTALQTD(Sales[Sales_Amount], Sales[Sales_Date])
The TOTALQTD function returns the total for the quarter to date for the specified column in the current context.
Which DAX function compares data between the current month and the previous month?
A. TOTALYTD()
B. LASTMONTH()
C. SAMEPERIODLASTMONTH()
D. PREVIOUSMONTH()
D. PREVIOUSMONTH()
The DAX function that compares data between the current month and the previous month is PREVIOUSMONTH().
The SAMEPERIODLASTMONTH function is wrong because it returns a table that contains a column of dates shifted one month back in time, but it does not compare data between the current month and the previous month.
Which of these DAX function categories loops through the same calculation on each row of a table, then aggregates the results?
A. Logical Functions
B. Filter functions
C. Statistical Functions
D. Iterator Functions
D. Iterator Functions
The DAX function category that loops through the same calculation on each row of a table, then aggregates the results is Iterator Functions.
You build a report to analyze customer transactions from a database that contains the tables shown in the following table.
Customer Table:
- Customer ID
- Name
- State
- Email
Transaction Table:
- Transaction ID
- Customer ID
- Date
- Amount
You import the two tables. Which of these two tables is the Fact table?
A. Customer table
B. Transaction Table
C. Neither Customer table nor transaction table
B. Transaction Table
The transaction table contains numeric values involving transaction details like date, amount. The customer table is the dimension table that contains less numeric values and more of categorical values.
You build a report to analyze customer transactions from a database that contains the tables shown in the following table.
Customer Table:
- Customer ID
- Name
- State
- Email
Transaction Table:
- Transaction ID
- Customer ID
- Date
- Amount
You import the two tables. What is the primary key of the Transaction table?
A. CustomerID
B. TransactionID
C. Amount
D. Date
B. TransactionID
The TransactionID is unique for each record in the transaction table, while CustomerID can repeat, since there can be multiple transactions performed by a single customer.
You build a report to analyze customer transactions from a database that contains the tables shown in the following table.
Customer Table:
- Customer ID
- Name
- State
- Email
Transaction Table:
- Transaction ID
- Customer ID
- Date
- Amount
You import the two tables. Which relationship should you use to link the tables?
A. Many-to-Many between Customer and Transaction
B. One-to-Many between Customer and Transaction
C. One-to-Many between Transaction and Customer
D. One-to-One between Customer and Transaction
B. One-to-Many between Customer and Transaction
This is because, one customer can make multiple transactions, while each transaction can be performed by only one customer.
Hence, the relationship between the customer table and the transaction table is one to many.
You plan to get data for a Power BI dataset from flat files.
You need a location type that requires you to provide credentials for a Microsoft account. Which location type should you recommend?
A. OneDrive for Business
B. local file
C. SharePoint - Team Sites
D. OneDrive personal account
A. OneDrive for Business
Uploading a local file will not require providing the M365 credentials. Files cannot be uploaded from a OneDrive personal account.
You have a data model that contains many complex DAX expressions. The expressions contain frequent references to the RELATED and RELATEDTABLE functions.
You need to recommend a solution to minimize the use of the RELATED and RELATEDTABLE functions. What should you recommend?
A. Merge tables by using Power Query
B. Hide unused columns in the model
C. Split the model into multiple models
D. Transpose
A. Merge tables by using Power Query
Combining data means connecting to two or more data sources, shaping them as needed, then consolidating them into a useful query.
When you have one or more columns that you’d like to add to another query, you merge the queries.
You are working with a Power BI report and need to show the percentage change in sales between the current month and the previous month. Which DAX function would be most suitable for calculating this percentage change?
A. TOTALYTD()
B. PARALLELPERIOD()
C. SAMEPERIODLASTYEAR()
D. PREVIOUSMONTH()
B. PARALLELPERIOD()
PARALLELPERIOD will show the percent change. Previous month wil show prior month sales but won’t include percent change between this month and last month.
What is the purpose of the USERPRINCIPALNAME() DAX function in Power BI?
a) To retrieve the current date and time.
b) To return the username of the person currently viewing the report.
c) To calculate the principal component analysis of a dataset.
d) To filter data based on user roles.
b) To return the username of the person currently viewing the report.
The USERPRINCIPALNAME() DAX function returns the User Principal Name (UPN) of the current user. This is often used for implementing row-level security (RLS) and personalizing reports.
