Microsoft Mock Exam Flashcards
You are creating a Power BI dashboard.
You need to apply a custom theme to the dashboard from another Power BI dashboard theme.
Which type of file should you use?
CSV
JSON
XML
YAML
JSON
You plan to schedule a refresh of DS1.
What is the number of consecutive refresh failures of DS1 that will automatically disable the refresh schedule?
Select only one answer.
2
4
16
64
4
You manage a Power BI workspace.
You need to delegate the task to update workspace metadata. The solution must use the principle of least privilege.
Which role should you use?
Select only one answer.
Admin
Contributor
Member
Viewer
Admin
You manage a Power BI workspace.
You need to delegate the task to schedule data refreshes. The solution must use the principle of least privilege.
Which role should you use?
Select only one answer.
Admin
Contributor
Member
Viewer
Contributor
The Contributor role is the least privileged role that grants permissions to schedule data refreshes. The Member role grants permission to schedule data refreshes but is more privileged than Contributor. The Admin role grants the permissions to schedule data refreshes but is more privileged than Member. The Viewer role does not grant the permissions to schedule data refreshes.
You have a Power BI Premium per user workspace.
What is the maximum number of refreshes that can be scheduled each day?
Select only one answer.
1
8
16
48
48
Power Bi allows 8 refreshes in Pro and 48 in Premium.
You have several on-premises Microsoft SQL Server databases.
You need to provide Power BI Service users access to the data sources without exposing the database servers directly to the internet.
The solution must minimize the configurations that must be performed by each user.
What should you deploy?
Select only one answer.
a virtual network data gateway
an ExpressRoute connection
an on-premises data gateway
an on-premises data gateway (personal mode)
an on-premises data gateway
You plan to get data from flat files for a Power BI dataset.
You need to select a location to store the files.
Which location requires an on-premises data gateway?
Select only one answer.
OneDrive for Business
Personal OneDrive account
SharePoint Online Team Sites
Shared folder on a local network
Shared folder on a local network
The shared folder on a local network will require a gateway as it is not available to the internet. SharePoint Online, OneDrive, and OneDrive for Business all can be refreshed as cloud data sources without a gateway.
You plan to add data to Power BI Desktop from a new data source. You are evaluating whether you should use the DirectQuery storage mode or the Import storage mode.
What are two benefits of using DirectQuery instead of Import? Each correct answer presents a complete solution.
Select all answers that apply.
full support for the Q&A Power BI service
full support for the Quick Insights Power BI service
minimized local disk space usage
minimized need for data refresh
minimized local disk space usage
minimized need for data refresh
True/False
Direct Query is not fully supported with the Q&A and Quick Insights Power BI services.
True
You plan to add data to Power BI Desktop from a new data source. You are evaluating whether you should use the DirectQuery storage mode or the Import storage mode.
What are two benefits of using Import instead of DirectQuery? Each correct answer presents a complete solution.
Select all answers that apply.
full support for the Q&A Power BI service
full support for the Quick Insights Power BI service
minimized local disk space usage
minimized need for data refresh
support for per table configuration
full support for the Q&A Power BI service
full support for the Quick Insights Power BI service
You have multiple Excel files stored in a folder synced with OneDrive for Business.
You need to import data from the files. The solution must NOT use a data gateway.
Which type of connector should you use? Select only one answer.
Excel workbook
Folder
SharePoint folder
SharePoint list
SharePoint folder
You create a Power BI data source which uses a SQL SELECT statement. The SQL statement queries multiple tables in a SQL Server database and includes subqueries.
When importing data from the data source into Power BI, you receive the following error message: “Timeout expired.”
You verify that network connection to the SQL Server has sufficient available bandwidth and low latency.
You need to minimize the occurrences of the timeout issues indicated by the message.
What should you do? Select only one answer.
Divide the SQL statement into separate data sources.
Implement aggregations in the SQL statement.
Implement groupings in the SQL statement.
Replace subqueries with nested queries.
Divide the SQL statement into separate data sources.
Dividing the SQL statement into separate data sources would minimize the amount of processing on the SQL Server side. This would minimize or even eliminate the timeout issues. Groupings, aggregations, and using nested queries would either have no impact on timeout issues or further increase the amount of processing on the SQL Server side
You create a Power BI data source which uses a SQL SELECT statement. The SQL statement queries multiple tables in a SQL Server database and includes subqueries.
After you import data from the data source into Power BI, you notice that one of the columns in the resulting dataset appears blank. You verify that the source table does include data.
What should you do to resolve the issue? Select only one answer.
Clear permissions on the data source in Power BI.
Use the CAST function in the SQL statement.
Use the DATALENGTH function in the SQL statement.
Set the privacy levels on the data source.
Use the CAST function in the SQL statement.
The issue indicates that Power BI is incorrectly interpreting the data type used by the source column. To resolve it, you need to explicitly specify the intended data type, which can be done by using the CAST function.
Using the CAST function in SQL ensures that the column is explicitly converted to a compatible data type (e.g., VARCHAR, INT, or FLOAT), making it readable in Power BI.
From Power BI Desktop, you create a data source by importing an Excel file.
You plan to identify data anomalies within the data source.
Which interface should you use to display the column distribution and column quality graphs?
Select only one answer.
Advanced Editor in Power Query Editor
Power Query Editor
the Data view
the Model view
Power Query Editor
Which interface in Power BI provides the ability to work with the M code used for shaping data?
Select only one answer.
Advanced Editor in Power Query Editor
Power Query Editor
the Data view
the Model view
Advanced Editor in Power Query Editor
You import an Excel file into Power BI Desktop and begin to analyze the data in Power Query Editor.
You need to identify outliers in a text column within the data source.
Which information should you use from Power Query Editor?
Select only one answer.
the min and max values in Column profile
the top and bottom entries in Value distribution
the value of the Distinct entry in Column statistics
the value of the Unique entry in Column statistics
the top and bottom entries in Value distribution
The top and bottom entries in Value distribution identify outliers, which appear, respectively, the greatest and the smallest number of times in that column.
From the Power Query Editor, you import data from a .csv file. The data includes a column named ZIP that contains postal codes from the United States.
You notice that Power Query Editor automatically applies the Whole Number data type to the ZIP column.
You need to ensure that the ZIP column uses the Text data type and that all values remain 5 characters long.
What should you do?Select only one answer.
A. From the data view in Power BI Desktop, change the column data type from number to text.
B. From Power Query Editor, add a new applied step at the end of the query to convert the ZIP column from number back to text.
C. From Power Query Editor, delete the changed type step.
D. From Power Query Editor, update the current changed type step and replace convert from number to text for the ZIP column.
D. From Power Query Editor, update the current changed type step and replace convert from number to text for the ZIP column.
To correctly update the data to text you need to replace the number type conversion with a text conversion, and to keep all other data type column transformations. This needs to be done in the Power Query Editor. Adding a new applied step at the end of the query would result in loosing zip codes that start with 0
You have a Power BI model with the following fact tables and storage modes.
FactStoreSales (Import mode)
FactOnlineSales (DirectQuery mode)
You have a dimension table named DimCalendar that has a relationship to both fact tables.
You need to assign a storage mode for DimCalendar. The solution must minimize the time to execute queries that combine data from the dimension table and the fact tables.
Which storage mode should you use? Select only one answer.
A. DirectQuery
B. Dual
C. Import
D. none
Dual
Using Dual mode means that either an import query can be run when accessing data from FactStoreSales, or a DirectQuery query can be run when accessing data from FactOnlineSales. Using Import mode means the queries are only optimized for FactStoreSales (Import).
You need to prevent hidden date tables from being auto generated by Power BI Desktop for every date or datetime data type column in a dataset.
What two tasks should you perform? Each correct answer presents a complete solution
Select all answers that apply.
A. Enable Mark as date table for the Calendar table.
B. From the Current File options in Power BI Desktop, disable Auto Date/Time
C. From the Global options in Power BI Desktop, disable Auto Date/Time for new files.
D. Set the Data Category to None for all Date and DateTime fields.
A. Enable Mark as date table for the Calendar table.
B. From the Current File options in Power BI Desktop, disable Auto Date/Time
Disabling Auto Date/Time for new files from the Current File options will disable all Auto Date/Time tables in this dataset. Enabling Mark as date table for the Calendar table will also disable the auto datetime tables in the dataset. Disabling the Global option Auto Date/Time for new files means that new files will no longer have Auto Date/Time enabled, but the file containing the current dataset will still have it enabled until it is disabled
You have a Power BI model. You need to assign items to a display folder.
Which three items can be assigned to a display folder? Each correct answer presents part of the solution.
Select all answers that apply.
A. Calculated column
B. Column
C. Measure
D. Report
E. Table
A. Calculated column
B. Column
C. Measure
A display folder is found in the Model View of Power BI. Tables and reports cannot be assigned a display folder since they aren’t within the model view. Columns, calculated columns, and measures can be assigned a display folder.
You need to create a new hierarchy in Power BI Desktop.
What should you do first? Select only one answer.
A. From the Model view, drag-and-drop one column onto another column in the Fields pane.
B. From the Model view, right-click and select Create hierarchy.
C. From the Report view, drag-and-drop one column onto another column in the Fields pane.
B. From the Model view, right-click and select Create hierarchy.
You create a data model in Power BI Desktop that contains DAX calculated columns and measures. You now need to create a report.
In which two places can a DAX calculated column be used, but a DAX calculated measure cannot be used? Each correct answer presents a complete solution.
Select all answers that apply.
A. as a filter in the “Filters on this page” well of the Filters pane
B. as a filter in the “Filters on this visual” well of the Filters pane
C. as an item in the “Add drill-through fields here” well of the Visualizations pane
D. as an item in the Fields well of a slicer
A. as a filter in the “Filters on this page” well of the Filters pane
D. as an item in the Fields well of a slicer
Unlike a measure, a calculated column can be used in a slicer to place filter options on the report page. DAX measures cannot be placed in the “Filters on this page” well. They can only be placed per visual, in the “Filters on this visual” well of the Filters Pane. Both DAX columns and measures may be used as a visual-level filter. Both DAX columns and measures can be used in the drillthrough well.
In Power BI Desktop, you need to create a measure.
Which two interfaces can you use? Each correct answer presents a complete the solution.
Select all answers that apply.
A. Data view
B. Page view
C. Power Query Editor
D. Report view
A. Data view
D. Report view
You have a Power BI Desktop dataset that includes a table named Warehouse. The Warehouse table includes a column named Inventory Count, which contains the current number of items for each row of a particular type on a given day.
You have the following Data Analysis Expression (DAX) query that calculates the sum of all values in the Inventory Count column of the Warehouse table:
Current Inventory Count = CALCULATE (
SUM ( ‘Warehouse’[Inventory Count] ))
You need to ensure that Current Inventory Count returns only the current total number of inventory items, rather than the sum of all inventory items that includes item counts from previous days.
What DAX function should you include in the query? Select only one answer.
A. CALENDAR
B. CALENDARAUTO
C. DISTINCTCOUNT
D. LASTDATE
D. LASTDATE
The LASTDATE function will ensure that the SUM function applies only to the last date of the time period, resulting in a semi-additive behavior.
You need to reduce the cardinality of a column in a Power BI dataset.
What should you do? Select only one answer.
A. Add a prefix to each value in the column.
B. Add a suffix to each value in the column.
C. Replace null values with a value of 0.
D. Reduce the number of distinct values
D. Reduce the number of distinct values
A column that has a lot of repeated values and a low number of distinct values will have a low cardinality. Reducing the number of distinct values reduces cardinality. Replacing nulls does not change the number of distinct values. A static prefix or suffix does not change cardinality.
Q1: If a question is asking about making an improvement to storage size, what storage mode is best?
Q2: If a question is asking about making an improvement to performance and processing speed, which storage mode is best?
A1: Direct Query is better storage mode to reduce the data model size because it is stored locally.
A2: Import mode is a better storage mode to improve performance. Since the data is stored in Power BI, it runs much faster.
You are designing a data model for Power BI Desktop.
You need to reduce the model size. Which technique should you use to reduce the model size?
Select only one answer.
A. Configure the Query reduction option Filters settings.
B. Configure the Query reduction option Slicers settings.
C. Set the Storage Mode property of tables to Direct Query.
D. Set the Storage Mode property of tables to Import.
C. Set the Storage Mode property of tables to Direct Query.
You decide to remove unnecessary columns from your data model.
What are two potential performance benefits of doing this? Each correct answer presents a complete solution.
Select all answers that apply.
A. decreasing report page load times
B. increasing DAX performance
C. increasing the refresh speed
D. reducing the size of the data model
C. increasing the refresh speed
D. reducing the size of the data model
Fewer columns mean there is less data to import and will reduce the model size and decrease the time it takes to refresh the model.
You have a Power BI Desktop dataset based on a star schema data source.
You need to use Data Analysis Expression (DAX) to create a date table in the dataset and populate the table with a range of dates based on the earliest and latest date within the dataset.
Which function should you use?
Select only one answer.
A. CALENDAR
B. CALENDARAUTO
C. DATE
D. DATEDIFF
B. CALENDARAUTO
Which function generates a range of dates, but requires providing the start and end date.
A. CALENDAR
B. CALENDARAUTO
C. DATE
D. DATEDIFF
A. CALENDAR
Which function generates a range of dates based on the earliest and latest date within the dataset.
A. CALENDAR
B. CALENDARAUTO
C. DATE
D. DATEDIFF
B. CALENDARAUTO
Which function returns an interval between two dates.
A. CALENDAR
B. CALENDARAUTO
C. DATE
D. DATEDIFF
D. DATEDIFF
Which function returns the specified date in the datetime format.
A. CALENDAR
B. CALENDARAUTO
C. DATE
D. DATEDIFF
C. DATE
In M Code, which symbol is used to define literal values that have special formatting, such as dates, durations, and nullable values.
Give an example:
date(2024, 3, 20) // Creates a date literal (YYYY, MM, DD)
symbol
#date(2024, 3, 20) // Creates the literal date: March 20, 2024
In M Code, which symbol is used when calling functions, accessing fields, or referring to properties within records or tables. This is specifically used in functions and formulas.
Give an example:
. (period)
You need to create a column in Power Query that extracts the year from a date column named [TransactionDate].
You use the M code: Date.Year([TransactionDate])
Which visualization type displays a running total, with the initial and the final value columns starting on the horizontal axis?
Waterfall Chart
Which visualization type displays a linear process with sequentially connected stages, with one stage transitioning to the next?
Funnel
B.You need to create a visual that displays sales by employees, trending over months.
The visual must clearly show how employees are performing against each other and have a ranking for each period.
Which visual should you use? Select only one answer.
A. clustered bar chart
B. ribbon chart
C. scatterplot
D. treemap
B. ribbon chart
A ribbon chart places the highest (ranked) value at the top of the stacked column each month and shows those ranked changes over time.
The clustered bar chart can be used to show changes over time, and a clustered bar chart will show comparisons between employees, but no ranking data is provided between employees.
You are modifying a report in Power BI Desktop. The report contains a single report page that has three card visuals and one clustered bar chart.
You need to create a bookmark that displays only the clustered bar chart and hides the cards. The solution must minimize development effort.
What should you do first? Select only one answer.
A. Add a bookmark in the Bookmarks Pane.
B. Hide the three cards in the Selection Pane.
C. Publish the report to the Power BI Service.
D. Select the Spotlight option on the clustered bar chart.
B. Hide the three cards in the Selection Pane.
You are using Power BI Desktop to edit a stacked column chart that displays gross sales data with a date hierarchy that contains year, quarter, month, and day.
You need to prevent users from viewing the hierarchy based on the year.
What should you do? Select only one answer.
A. In the Filters pane, add a filter based on the quarter, month, and day.
B. In the Filters pane, add a filter based on the year.
C. In the Format pane, modify the Maximum Range setting.
D. In the Visualization pane, remove the year field from the date hierarchy fields in the X-axis.
D. In the Visualization pane, remove the year field from the date hierarchy fields in the X-axis.
To prevent users from viewing the hierarchy based on the year, you remove just the year from the date hierarchy fields in the x-axis in the Visualization pane. The quarter, month, and day will remain populated and usable.
Adding a filter based on the year in the Filters pane would filter the values displayed in the visual,
You plan to use Power BI Desktop to analyze sales data of your company.
You need to identify two types of data you will be able to use for creating bins that group the sales data.
Which two data types should you identify? Each correct answer presents a complete solution. Select all answers that apply.
A. Binary
B. Date/time
C. Numeric
D. Text
E. True/false
B. Date/time
C. Numeric
Date/time and numeric data types support bins-based grouping. Binary, Boolean, and text data types do not support bins-based grouping.
You need to create a histogram that will display ranges of integers in groups.
Which two resources do you need to create the histogram? Each correct answer presents part of the solution.
Select all answers that apply.
A. a Bin group type
B. a Date/time column
C. a List group type
D. a Numerical column
A. a Bin group type
D. a Numerical column
You need to add a new visual to a Power BI Dashboard. This visual does NOT exist on a report in the workspace.
What should you do first? Select only one answer.
A. Open See Related Content.
B. Open the File menu.
C. Select Add a tile.
D. Select Ask a question about your data.
D. Select Ask a question about your data.
he Q&A feature lets you create a visual by typing in a question about your data.
