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