PowerBI Certification Exam Prep 2

You have the following Power Query M formula that generates a range of dates. = List.Dates(#date(2020,05,31), 365, #duration(1,0,0,0)) What is the resulting date range?

A list of days starting on May 31, 2020 and ending 365 days later. The #date element designates the starting date, 365 designates the duration counter and #duration(1, 0, 0, 0) designates the duration interval in days, so this formula results in a listing of days from May 31, 2020 and ending 365 days later.

In Power BI Desktop, you plan to use M-language to define a common date table spanning a period of 10 years. You need to identify the M language function that would allow you to specify that rows in the table should represent consecutive days within the date range you designated. Your solution must minimize administrative effort. Which syntax should you use?

#duration The #duration function of the M language allows you to specify the datetime values that will be entered into individual rows of a date table. The #date function creates a date value based on the date parameters you specify. The List.Combine() combines multiple lists into one. List.Durations returns a list of count duration values, rather than dates.

You manage a Power BI workspace in shared capacity. The workspace contains a dataset named DS1. You plan to schedule the refresh of DS1. What is the maximum number of data refreshes per day?

8 The maximum number of refreshes per day for a Power BI workspace in a shared capacity is 8.

You manage a Power BI workspace in shared capacity. The workspace contains a report that uses a dataset named DS1. 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?

4 If there are 4 consecutive data set refresh failures, the refresh schedule will be automatically disabled.

You implement the Q&A feature in a report, so users can get answers to their questions on their own. You need to improve your Q&A search capabilities for end-users. Which two configurations can you add? Each correct answer presents a complete solution.

Add synonyms to model fields. Add linguistic schema to the dataset. Adding synonyms to model fields will help users search for them. For example, you can give a synonym of (Actuals) for the (Sales) measure. A linguistic schema describes terms and phrases that Q&A should understand for objects within a dataset, including parts of speech, synonyms, and phrasings that relate to that dataset. Composite models are not supported by Q&A today. Organizing fields by display folders will not impact Q&A search capabilities.

You use Power BI Desktop to transform a query that retrieves data from an Excel spreadsheet. you need to view and modify multiple steps in all of the M code. Which interface should you use?

Advanced Editor in the Power Query Editor The Advanced Editor in the Power Query Editor provides the ability to work with all of the M code used for shaping data in a query at once. Data view provides access to data within a dataset. Model view provides access to the data model of a dataset. The Formula Bar in the Power Query Editor provides the ability to edit only one step at a time.

You have a collection of datasets and reports. You need to share the datasets and report together. This solution must NOT send any email notifications to internal users after sharing. What should you create in the Power BI Service?

An app Workspace apps can be installed automatically for any user in an organization, and without requiring notifications of installation. Deployment pipelines are used for moving artifacts between test, development, and production environments. Metrics are where you can create goals based off existing datasets. You can add members to a workspace, but they receive a notification that they've been added to the workspace.

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?

An on-premises data gateway An on-premises gateway is designed to allow multiple users to access multiple data sources. An on-premises data gateway only allows one user to access multiple data sources. A virtual network data gateway is designed to allow multiple users to access multiple online data sources. However, it isn't installed locally and only works with data sources secured by virtual networks.

You are creating a report in a Power BI Desktop by using a dataset that contains sales data. You need to create a measure that always provides the value of total sales for the year 2022, regardless of which year is selected in any visual in the same report. Which DAX function should you use in combination with the SUM function to override the context and provide the result?

CALCULATE The CALCULATE function provides the result of the calculation with the ability to override the context. The IGNORE function modifies the behavior of the SUMMARIZECOLUMNS function by omitting specific expressions from the BLANK/NULL evaluation. The FILTER function returns a table that represents a subset of another table or expression. The SUMX function returns the sum of an expression evaluated for each row in a table.

You need to enhance a data model by using Power BI Desktop. Data for which two model items can be created using the DAX language? Each correct answer presents a complete solution.

Calculate Table Numeric Range Parameter Calculated tables are generated with DAX queries. Numeric range parameters create a table and measure both generated with DAX queries. The enter data button creates a manual table using the M language and Power Query. Display folders are a way to visually organize measures, columns, or hierarchies. DAX is not used to create them.

You have a Power BI Dataset. You need to set the dataset as discoverable. Which two configuration will allow the dataset to be marked as discoverable? each correct answer presents a complete solution.

Certify the dataset Promote the dataset A certified dataset can be configured to be discoverable for users without access to request permissions to access. A promoted dataset can be configured to be discoverable for users without access to request permissions to access. RLS is not required on a dataset to become discoverable. Premium (capacity or shared) is not required to make a dataset discoverable.

You plan to use the calculated table functionality to add a duplicate table in Power BI Desktop. Which characteristics of the original table will be duplicated?

Data Only A calculated table only duplicates data. Any model configurations such as column visibility or hierarchies must be recreated if needed.

In Power BI Desktop, you need to create a measure. Which two interfaces can you use? Each correct answer presents a complete solution.

Data View Report View The Report view provides the ability to create measures. To create a measure, use the context sensitive menu of the Fields list or the Calculations section of the ribbon. The Data view provides access to data within a dataset and includes the option to create a measure in the Calculations section of the ribbon. Model view, Page view, and Power Query Editor do not include the option to create a measure.

You need to create a visual that will display information on a continuous axis. Which two data types can be used to display a continuous axis on a visual? Each correct answer presents a complete solution.

Date Decimal Number Date data types display well on a continuous (range) axis where report users understand chronological order. Number data types display well on a continuous (range) axis where report users understand the order between min and max values. Binary data and text (categorical) data cannot be displayed on a continuous (range) axis. Date, time, or number are the only data types comparable with a continuous axis.

You are editing a report in Power BI Desktop. You need to adjust the behavior of the report by editing interactions. Which two types of interaction behavior can you change between visuals? Each correct answer presents a complete solution.

Filter Highlight A filter will show you the filtered data in this visual. Highlight is the default interaction between visuals. It shows you both the unfiltered and filtered values in the visual, for comparison purposes. Drillthrough is a page navigation experience that takes you from one page to another plus applies a set of filters to page navigated to. Expand is a way to navigate down a level using the hierarchy controls.

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?

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, resulting in more frequent timeout issues.

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?

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). Using DirectQuery mode means the queries are only optimized for FactOnlineSales (DirectQuery). A storage mode of Import, DirectQuery, or Dual must be assigned.

You need to make changes to your data sources. Which three changes are supported by the data source settings in the Power Query interface? Each correct answer presents a complete solution.

Editing Permssions Modifying the File Path Clearing Permissions The Data Source Settings in the Power Query interface supports editing permissions, clearing permissions, and modifying the path of the data source file. Any structural changes to a file, such as adding, removing, or renaming columns are not supported by the Data Source Settings in the Power Query interface.

You need to create a custom Python visual by using Power BI Desktop. What do you need to do first?

Enable the script visuals option in the Visualization pane of the Power BI Desktop. Enabling the script visuals option in the Visualization pane of Power BI Desktop is required before creating custom Python visuals in Power BI Desktop. Installing Python is not required. Configuring global Python scripting options in Power BI Desktop is not required to create Python visuals. The ability to create a custom Python visual by using Power BI Desktop has no dependency on enabling preview features.

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?

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. Changing the data type in Data View is equivalent to adding an applied step at the end of the query and would not preserve leading zeros. Deleting the changed type step would not set the data type to Text.

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 should you do?

From the Current File options in Power BI Desktop, disable Auto Date/Time for new files. 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 only disable the auto datetime tables for this table. Other date or datetime columns in the model will still have auto datetime tables associated with them. Disabling the Global option Auto Date/Time for new files means that new files will no longer have Auto Date/Time enabled, but files in the current dataset will still have it enabled until it is disabled. Changing the data category will not impact the auto date table feature.

You need to create a new hierarchy in Power BI Desktop. What should you do first?

From the Model view, right-click and select Create hierarchy. To create a new hierarchy in Power BI Desktop, you must select Create hierarchy from the Model view. The option to create hierarchies from the Report view was removed as an option in 2021 because too many hierarchies were being accidentally created during development. You cannot drag-and-drop one field onto another to create a new hierarchy in the model view. You can only use this method to add additional fields to an already existing hierarchy.

You plan to create a report in Power BI Desktop that will display sales opportunity by five sales stages, including lead, prospect, qualified, committed, and transacted. You need to identify the type of visualization that will display a linear process with sequentially connected stages, with one stage transitioning to the next. Which visualization should you use?

Funnel A funnel visualization displays a linear process with sequentially connected stages, with one stage transitioning to the next. A slicer visualization is available in different formats, including list, drop-down, and buttons, but not sequentially connected stages, with one stage transitioning to the next. A treemap visualization displays data as a set of nested rectangles. A waterfall visualization displays a running total as values are added or subtracted.

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?

Hide the three cards in the Selection Pane. Before you create a bookmark, you need to configure the report page to show the state you want to capture in the bookmark. Visuals are visible by default, therefore, you must mark them as hidden. To add a bookmark, you first need to update the bookmark after hiding the visuals. Bookmarks can be created in Power BI without publishing to the Power BI service. While spotlight does focus on the clustered bar chart, it does not completely hide the cards.

You decide to start using variables when creating DAX formulas. What are two benefits of using variables in DAX measures over using original expressions? Each correct answer presents a complete solution.

Improves the overall readability of the resulting DAX measure Improves the overall performance of the resulting DAX measure Variable names are often shorter than the original expressions, and the final variable returned at the end of the measure will be more readable with variable names included. Variables cache the defined calculation, allowing it to be referenced multiple times without additional impacts from recalculations. The functions in DAX are universal, and there are no specific functions that can only be used within variables. The data source connection is set in the model and cannot be changed from within a DAX measure.

You manage a Power BI workspace in shared capacity. The workspace contains a report that uses a dataset named DS1. You need to implement an incremental refresh of DS1. What should you do first?

In Power BI Desktop, define the filter parameters. To implement an incremental refresh of the dataset, you first need to define an incremental refresh policy, which, in turn, requires defining filter parameters. An incremental refresh policy does not require upgrading to a Premium SKU. Publishing the dataset to the Power BI service is required for the incremental refresh policy to take effect, so it's the last step of setting up an incremental refresh.

You have implemented role-based security that restricts access to a Power BI Desktop dataset for users who are assigned the role named Salespeople. You need to validate that the implementation works. What should you do?

In the Report view, activate the "View As" feature. Activating the View as feature will allow you test the row-level security as someone with the Salesperson role. A what-if parameter is intended for visualization and analytics of results of changing a value of a variable, not validating row-level security. Adding a DAX expression filter to the Salesperson table is part of implementing row-level security, which is already completed. A conditional column is computed from other columns or values.

You need to create a custom R visual by using Power BI Desktop. What do you need to do first?

Install R on your computer. To create a custom R visual by using Power BI Desktop, you first need to install R on your computer. Configuring global R scripting options in Power BI Desktop might be required once you install R on your computer. Enabling the script visuals option in the Visualization pane of Power BI Desktop is done once R is installed and configured using the global R script options in Power BI Desktop. Creating a custom R visual by using Power BI Desktop has no dependency on enabling preview features.

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?

JSON To copy themes between workspaces, you can download and upload them as JSON files. Themes use the JSON file format not CSV, YAML, or XML

You plan to get data for a Power BI dataset from flat files. You need a location type to store the files. The date must NOT automatically synchronize between the original file and the dataset. Which location type should you recommend?

Local File The local file option will result in importing the file contents without providing automatic synchronization between the original file and the dataset. SharePoint - Team Sites, personal OneDrive accounts, and OneDrive for Business provide the ability to synchronize flat files and Power BI datasets.

You have designed a star schema to simplify your data. You need to understand the relationship between the tables in the star schema. What is the relationship between the fact tables and dimension tables?

Many-to-One There is a many-to-one relationship between a fact table and dimension table. For each row in a dimension table there may be multiple matching rows in the fact table.

You plan to create a report in Power BI Desktop. You need to create a visualization that displays data in a two or more dimensions and cross-highlights with other visuals on the same report page. Which type of visualization should you use?

Matrix A matrix visualization displays data in two or more dimensions and cross-highlights with other visuals on the same report page. A card visualization displays a single data point. A scatter visualization is a chart, not a grid. It has two value axes, with one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis. A table visualization is a grid that contains related data in a grid format, with support for two dimensions only.

You plan to build a Power BI report and set up conditional formatting of data fields. You need to identify two visuals that support the conditional formatting functionality. Which two types of visuals should you identify? Each correct answer presents a complete solution.

Matrix Table Matrix and table visuals support conditional formatting. Gauge chart, scatter chart, and treemap do not support conditional formatting.

You have an Azure SQL database that contains two tables named SalesOrders and SalesOrderDetails. You load the SalesOrders and SalesOrderDetails tables into Power BI Desktop. The tables have a relationship based upon the sales order ID. You need to combine the two tables into one table. The solution must meet the following requirements: There is a row for every sales order detail row in the resulting table, even if there is no corresponding sales order row. For every sales order detail row, the row in the resulting table includes the corresponding sales order row data if the sales order row exists. Any sales order row data that does not have corresponding sales order details is not included in the resulting table. What should you do to combine the two tables?

Merge the tables using the left outer join kind based on the sales order id To construct the resulting table, it is necessary to merge tables using the left outer join kind with the SalesOrderDetails as the first table. Appending tables in this context would not make sense, since they do not have the same columns. Merging using the inner join kind would not produce a table that includes a sales order details row where there is no corresponding sales order row. Full outer join would produce a table that includes sales order row data that do not have corresponding sales order details.

You have an Excel spreadsheet that contains two columns as follows: -Category: Contain names of categories -Subcategory: Contain names of subcategories for each category You import the Excel spreadsheet into Power BI Desktop. You need to transform the data to meet the following requirements: Have multiple columns, where one column represents one category Have a single row that includes the total count of subcategories for each category Which transformation should you use?

Pivot Columns The pivot column operation converts data into a table by aggregating values in a column. In this case, you can pivot the columns to calculate the count of product subcategories in each product category. The unpivot column operation performs the opposite task, converting columns into rows. Renaming columns would result in a table with different column names but the same data content. Transposing would simply switch rows and columns, without the required aggregation.

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?

Power Query Editor Power Query Editor provides the ability to display the column distribution and column quality graphs. Data view provides access to data within a dataset. Model view provides access to the data model of a dataset. Advanced Editor of Power Query Editor provides the ability to work with the M code used for shaping data in Power Query Editor.

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.

Reducing the size of the data model Increasing the refresh speed 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. Since row counts aren't changing, the calculation speed of any existing DAX measures won't change. Report page load times are primarily determined by number of visuals (objects) on the page, and DAX performance. Neither of which are impacted by unnecessary model columns.

You plan to create a report in Power BI Desktop that will display the relationship between the number of orders and the number of orders shipped by product category. You need to identify the visual that will clearly identify outliers in the data set by displaying them away from the bulk of data. Which visual should you use?

Scatter A scatter visual displays a relationship between values associated with two axes: one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis. It is particularly suitable for identifying outliers because it displays them away from the bulk of data. A card visual displays a single data point. A treemap visual displays data as a set of nested rectangles. A gauge visual displays a circular arc including a single value that measures progress toward a goal or target.

You need to create a report in Power BI Desktop that will display the sales data of your company such that customers of a similar age will be grouped into clusters. Which visual should you add to the report?

Scatter Creating a scatter visual is the first step of applying the clustering technique that groups data into clusters. Card, gauge, and treemap visuals do not support clustering.

You are designing a data model for Power BI. You need to reduce the model size. Which technique should you use to reduce the model size?

Set the Storage Mode property of tables to Direct Query. Setting the Storage Mode property of tables to Direct Query reduces the model size since only schema of the data source is stored locally. Setting the Storage Mode property of tables to Import will import data into Power BI desktop, effectively increasing the model size. Configuring the Query reduction option Slicers or Filters settings has no effect on data model size.

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?

SharePoint Folder A SharePoint folder is the only connector that will allow the import of multiple Excel (or CSV) files stored in a OneDrive for Business folder, without using a data gateway. While you can connect to the folder on an on-premises device, it would require a data gateway to refresh in the service. An Excel workbook would only connect to a single Excel file and would also require a data gateway. A SharePoint list connector only connects to SharePoint lists and cannot connect to Excel files.

You plan to use Power BI Desktop to create a report with multiple visualizations. You need to create a visual that supports the following: -filters other visuals on the same report page -allows users to search for values by which to filter the other visuals Which type of visualization should you use?

Slicer The slicer visualization can be used to filter the other visuals on the page. You can enable a search box in a slicer where users can search for values to filter. A funnel visualization is a chart that that has sequential connected stages, where items flow sequentially from one stage to the next. A matrix visualization displays data in two or more dimensions and cross-highlights with other visuals on the same report page. A scatter chart visualization is a chart with one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis. The matrix, scatter chart, and funnel do not offer a search box.

You plan to use Power BI Desktop to analyze sales data for products sold by your company. You need to create a DAX formula that will list 10 best-selling products sorted by their total sales. Which DAX function should you use?

TOPN The TOPN function returns Top N rows of the specified table, such as, for example, top 10 best-selling products sorted by their total sales. The MAXA function returns the largest value in a column. The MAXX function evaluates an expression for each row and returns the largest value. The RANKX function returns ranking of a number in a list of numbers for each row of a target table.

You need to create a Power BI Dashboard. Which tool should you use?

The Power BI Service The Power BI service provides support for creating Power BI dashboards. The Power BI mobile app can be used to view dashboards, but not to create them. Power BI Desktop does not provide support for creating Power BI dashboards. Power Query is a Microsoft Excel tool that is used for importing data, but not for creating dashboards.

You plan to optimize the performance of Power BI Desktop queries against a remote data source. You need to disable the default behavior that automatically applies cross highlighting and filtering of visuals within the same report. Which option should you configure in Power BI Desktop?

The Reduce number of queries sent by Query reduction setting The Reduce number of queries sent by Query reduction setting disables the default behavior that automatically applies cross highlighting and filtering of visuals within the same report. The Slicers Query reduction settings allow you to instantly apply slicer changes and add an Apply button to each slicer. The Filters Query reduction settings allow you to instantly apply basic filter changes. The Persistent filters of Report settings allow you to prevent users from saving filters in the Power BI service.

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?

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. The value of the Distinct entry in Column statistics indicates the total count of different values. The value of the Unique entry in Column statistics indicates the total count of different values that appear only once. The min and max values in the Column profile of a text column designate the entries which appear, respectively, first and last in alphabetical order.

You plan to create a report in Power BI Desktop. You need to identify the type of visualization that displays data as a set of nested rectangles. Which visualization should you use?

Treemap A treemap visualization displays data as a set of nested rectangles. A slicer visualization is available in different formats, including list, drop-down, and buttons, but not nested rectangles. A bar chart visualization presents data as horizontal bars. A column chart presents data as vertical columns.

You have an Excel spreadsheet that contains three columns labeled Year, 2021, and 2012. The entries in rows for the first column consists of names of the individual months in the year while the other two columns contain the sales amount for each month for the corresponding year. You import data from the Excel spreadsheet into Power BI Desktop. You need to transform the data so it will consist of three columns, with the first one containing month, the second containing year, and the third containing the sales amount for that month and year. Which transformation should you use first?

Unpivot Selecting Unpivot will allow you to shape the current table into the one with the year, month, and sales amount columns, which will need to be renamed afterwards. Pivot would be applicable in the opposite scenario, in which flat data needs to be reorganized into one containing aggregate values for each unique value in each column. Transposing would switch tables and columns. Removing columns would result in a table with insufficient data to perform unpivot.

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?

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. DATALENGTH displays the number of bytes used to represent an expression. Clearing permissions could prevent Power BI from being able to access the target database. Setting the privacy levels of the data source would have no impact on addressing the issue of missing data.

You connect Power Query Editor to a database table. You need to remove the Row ID column. Your solution must ensure that new columns do NOT display in the table model during a scheduled refresh in the future. What transformations should you use?

Use the Select Column command and choose the columns to keep. Only the Select Columns command will let you choose columns to keep, delete the column you do not want, and prevent new columns from showing up in the table in the future.