Initially Unsure Questions
Which of the following options allows you to make a live connection to the data source? Choose 2.
- Analysis Services Tabular - Analysis Services Multi-dimensional
Which of the following is valid options for adding a KPI to a PivotTable report? Choose 3.
- Explicit measure for base field - explicit measure for target value - absolute value for target value
Which of the following is appropriate DAX when selecting New Column from Power Pivot?
- FullName = Customer[Prefix] & "" & Customer[FirstName] & "" & Customer[LastName] - TotalPrice = Sales[Qty] * Sales[Amount] - CurrentAge = DATEDIFF(Table[BirthDate], TODAY(), YEAR)
Once you have completed all transformations in Power Query, what steps can you take to optimise the data model outside of Power Query?
- Hide columns and tables required in measures, but not needed in analysis - choose the correct format for columns - add descriptions to tables and columns
Which of the following functions should not be used in calculated columns
- SUMX - SUM - COUNT
You have two tables as in the exhibit merged and loaded into the Excel data model. The M code for the merge is as follows = Table.NestedJoin(Points,{"Name", "Surname"},Bonus,{"Name", "Surname"},"Bonus",JoinKind.LeftOuter). You have a DAX measure as follows Total Points := CALCULATE ( SUM ( Merge1[Points] ) + SUM ( Merge1[Bonus Points] ) ) You create a PivotTable with Names in the row field and Total Points in the Values field. What will the PivotTable display under the Points column for Peter? Larger image
15
You have two tables as in the exhibit appended and loaded into the Excel data model. You have a DAX measure as follows Total Points := CALCULATE ( SUM ( Append1[Points] ) + SUM ( Append1[Bonus Points] ) ). You create a PivotTable with Names in the row field and Total Points in the Values field. What will the PivotTable display under the Points column for Peter?
25 points Explanation: Append creates a table containing all rows and columns from both tables. The DAX measure sums the points (from both tables) and for both columns (points and bonus points). The PivotTable creates a single row for each unique name. The result of the DAX measure for Peter on the PivotTable is the sum of the points from the first table and the appended second table and the bonus points from the second table, totalling 25. Scenario questions will often combine ares from all knowledge domains into a single question.
Which of these options cannot be pinned to a Power BI dashboard using Power BI publisher for Excel? Choose 2.
3D Map, Power View visual Explanation: You cannot pin 3D maps or Power View visuals using Power BI Publisher for Excel. You can pin PivotTables, PivotCharts, Ranges and any normal charts including Treemaps. You cannot connect the Treemap to the data model or use it as a PivotChart, but you can pin any normal chart to a Power BI dashboard using the Power BI publisher for Excel. Yes, really. :p
You enter the following DAX formula, what is the result? My Measure := "3" + "3"
6 Exaplantion: In DAX the operator (+) determines and automatically converts the data types of the fields as is required by the operator. Similarly My Measure := 3 & 3 would yield the same result (33) as My Measure := "3" & "3" Continue Retake test
Write a DAX calculated column formula that returns the 1st day of the month for each of the dates in the Date column in the Sales table.
= EOMONTH(Sales[Date],-1)+1
In the Days column of the Calendar table the names of days are listed. Your analysis requires three-letter abbreviated day names that are all caps. Write the appropriate DAX formula for the Short Day column.
=UPPER(LEFT(Calendar[Days],3))
This DAX function adds a temporary column to a table by calculating an expression. It then returns the average of the temporary column and removes the temporary column.
AVERAGEX(<table>,<expression>)
Which of the following is the correct syntax of the CALCULATE function?
CALCULATE(<expression>,<filter1>,<filter2>...)
Which time intelligence DAX function would you use to calculate a running total?
DATESINPERIOD
You use Get Data to connect to a .csv file containing racing car lap times including clock start, clock end and duration columns. You notice the duration column data type is Decimal Number. You change the data type to Duration and use Close & Load to load the data into the Excel Data Model. In Excel you click on Manage Data Model and click on the Duration column. What is the data type of the column?
Decimal Number
Which of the following options are not appropriate for sharing a report you completed in Excel using the data model?
Export to Power BI
You receive a Excel analysis workbook containing several PivotTables and PivotCharts built using data from an included data model made up of several queries to publicly accessible data sources. You open the workbook and start interacting with the PivotTables. During your exploration you click refresh on one of the PivotTables and Excel becomes unresponsive for a long time. What is the most likely cause of the issue?
Fast Data Load has been enabled for queries. Explanation: Switching on Fast Data Load allows the query to load faster by using foreground processing instead of background processing. Excel will be unresponsive during the time the query is executed and data is loaded. Use with caution.
What Power Query function can you use to load pre-aggregated data into the data model?
Group By
What is the M function used to interpret JSON?
Json.Document
When performing a Merge Queries in Power Query, which of the following join types are available?
Left Outer, Right Outer, Full Outer, Inner, Left Anti, Right Anti
Under what circumstances can two DAX measures have a duplicate name?
NONE OF THESE - when they reside in different tables - when they are used within a static data set - when they use fully qualified column references - when they reside in the same table
You use Get Data to connect to a data source and import the data into the data model. From Power Pivot, you need to change the data type of one of the columns. Which of the following are valid options?
None of the below options are correct. - date/time/timezone - duration - text - scientific number
You create the following two measures in the Sales table: Total Sales := SUM([Amount]) Total Tax := SUM([Tax]) You create the following measure in the Customers table: Total Sales := DISTINCTCOUNT(Sales[Amount]) Which of the following statement is true?
None of the options are correct Options included: - all three measures are explicit measures - the measure in the Customeres table calculates the total sales amounts in the Sales table - The measures in the Sales table use fully qualified column references
Where is the connection information stored when connecting to Analysis Services Database?
Office Data Connection file (.odc)
What filter direction options are available in Excel?
One way, from one-side to the many-side
You receive an Excel file from a colleague. You open the file and from the Power Pivot tab, you select Manage. You see a table containing product-related information with several columns. You notice that some of the column-headers have a black background while the others have a green background. What is the difference between these columns?
The black columns are DAX-formula calculated columns; the green columns are source data brought through from Power Query
Which of the following statements is false regarding the get external data function in Power Pivot?
The get external data function uses Power Query
You adjust the properties of a query in Power Query. Which of the following effects of switching on Fast Data Load is false?
The query is only executed for refreshes if the source data has changed.
Which of the following requirements for you to use the Analyze in Excel feature of Power BI Service is false?
You must install Power BI Gateway
Which of the following accurately describes the order of operations for a DAX measure? a) Evaluate filter context from visual b) Apply filter context to downstream tables c) Apply filter context from DAX formula d) Calculate result
a, c, b, d
Which statement about hierarchies is false?
A hierarchy cannot use hidden columns.
Which of the following data formatting can be applied to a PivotTable, but not to the dataset?
Accounting
You are adding a new column in Power Query by extracting a portion of the data in an existing column. What is the missing part of the M code? = Table.AddColumn(#"Renamed Columns", "Result", each _____________([Source], " "), type text)
Text.BeforeDelimiter
Which of the following is the correct naming of components of a DAX formula Total Sales := SUM ( Sales[Amount] )
"Total Sales" is the measure name ":=" separates the name from the expression "SUM" is the DAX function "Sales[Amount]" is a function argument
Which three of the answers are valid for what you observe in the attached exhibit taken from the calculation area of a data model you are working with?
- Ave Qty is used in a KPI - Ave Qty is an explicit measure - Sum of quantity is an implicit measure
Which of the following is correct for DAX measures, but not for DAX calculated columns?
- Filter Context - Recalculates when used - Row Context
You have started exploring and analysing a very large dataset in Excel using the data model and a PivotTable. Each time you change fields in the PivotTable it takes a long time for Excel to recalculate the results making the analysis painfully slow. What can you do to improve performance while you construct the report?
Activate Defer Layout Update on the PivotTable
What are the parameters you have to complete to connect to an Azure SQL Database?
- Firewall Rule (in Azure) - Username/Password - Server Name Explanation: You require the servername of the Azure SQL Database, login credentials (there are several different options here, username and password is one such option) and you require that a Firewall rule be configured on the Azure SQL Database for the IP address you will be using to connect to the database. You can also specify the database name, but this is optional and not required for you to make a connection. The table name might be useful, especially if there are many tables in the database to choose from, but is not a minimum requirement for you to technically make a connection to the data.
You have tables in Excel and need to display its content on Power BI Service dashboard. Which of the following is will accomplish your task? Choose 3.
- Install Power BI Publisher for Excel - Login to Power BI Service From Excel - Pin the table to a Power BI Service dashboard from Excel
You use Get Data to connect to a data source. You complete the connection details, supply the username and password and from the navigator, you select the tables you want to import to the Excel data model and click Load To... What option(s) do you select in the Import Data dialogue?
- Only Create Connection - Add this data to the Data Model
Which of the following Power BI Service integration options allows other users to view PivotTables you created in your Excel analysis workbook? Choose 3.
- Pin to Power BI - Upload from Power BI - Upload to Power BI
You use Get Data to connect to a data source. You select Load To and are presented with the Import Data dialogue. Which options will load the data into an Excel object?
- PivotChart - PivotTable Report - Table - Add this data to the Data Model
In PowerQuery you have a table with columns named Date, ProductID, CustomerID, Quantity and Price. You select the ProductID and CustomerID columns, then from the Transform tab you choose Group By. In the Group By dialogue box you choose Sum as the Operation and Quantity and the Column. What does the resulting data set look like?
- ProductID containing unique combinations with CustomerID - Quantity containing the sum of original quantities for matching ProductID and CustomerID values - CustomerID column containing unique combinations with ProductID
In PowerQuery, what is the effect on updated source data when you use Remove Columns vs Remove Other Columns?
- Remove columns removes the selected columns - remove columns causes new columns added to the data source later to appear in the data model - remove other columns removes the columns not selected
Consider the following data information Table Name: Sales Column Name: Amount Data type: Whole Number Sample value: 5678 Consider the following measure My Column := FORMAT(Sales[Amount], "Currency") What is the data type of the new column?
- Text
Which of the following statements is true regarding DAX functions?
- calculated columns are calculated when data refreshes - measures are calculated when used
Which of the following Power Query functions reduces the data loaded into the data model?
- group by - filter data - choose columns - remove columns
If there are generally two types of tables in data analysis: Fact (AKA data) tables and Dimension (AKA lookup) tables; Which of the following are true for Fact (AKA data) tables?
- has foreign keys - has many-side of one-to-many relationships - contains measurements
Which of the following types of cardinality is supported by the Excel Data Model?
- one to one - one to many
If there are generally two types of tables in data analysis: Fact (AKA data) tables and Dimension (AKA lookup) tables; Which of the following is true for dimension (AKA lookup) tables?
- one-side of one-to-many relationships - contains attributes - has primary keys
Which two ways can be used to optimise a dataset by reducing the number of rows?
- parameters - filter functions
You create a Power Query to a CSV file on your desktop, cleanse the data and load it into the data model. You create a few PivotTables to analyse the data. You save the .xlsx file, also to the desktop. You decide to move the .csv and .xlsx files to the My Documents folder. You attempt to open the .xlsx file. Which of the following statements are true?
- the .xlsl files opens but the query returns an error - the pivottables are functional - the query maintains an absolute path to the .csv file. Edit the query to point to the new location of the .csv file Explanation: .xlsx files open regardless of their path - just like any other Office file. The query maintains an absolute file path to the data source and will fail if the data is moved. However, one can easily adjust the source step in Power Query to the new path. Deleting and recreating the query will also require you to redo all the transformations - not optimal. Once data is loaded into the data model, it is persisted in the .xlsx file. A failed query doesn't destroy the data in the data model. Once the query is updated with the new source path - the query will succeed and the data in the model will be refreshed.
You create a PowerQuery to a CSV file on your desktop, cleanse the data and load it into the data model. You create a PivotTable and save the .xlsx file, also to the desktop. You decide to move the .csv and .xlsx files to the My Documents folder. You attempt to open the .xlsx file. Which of the following statements are true?
- the PivotTable is functional - the .xlsx file opens, but the query returns an error - the query maintains an absolute path to the .csv file. Edit the query to point o the new location of the .csv file
Under what circumstances would relationships between tables be created automatically in the Excel data model?
- when the source system supplies supporting metadata - when the include related tables checkbox is selected - when using the detect relationships function in Excel
The Sales[Month] column contains the month component of when a sale was made as a text field. You need the column sorted using the related Calendar[Month_Number] column. Which of the following is required to use the Sort by Column feature in Power Pivot?
Add the Calendar[Month_Number ] column to the Sales table using the RELATED DAX function.
Which of the following is not a valid file format supported by Get Data?
Binary (BIN) file
What manual step is necessary when connecting to a JSON or XML source that is usually handled automatically by PowerQuery?
Change column data types.
You create a PivotTable in Excel as part of analysing data that you added to the Excel data model. You have added several items to the Rows field well and notice that they are displayed nested in one column as in the exhibit. How can you change the PivotTable so that it keeps the Count of Name column but displays each row item in a separate column?
Change the PivotTable Report Layout to Outline Form
Which of the following are not results of using the Clear All command from PivotTable Tools, Analyze tab?
Clears the PivotTable cache
You have pinned a range to a Power BI dashboard using Power BI Publisher for Excel. You have subsequently updated the range of values. How is the Power BI dashboard updated?
Click Update from the Pin Manager
You have pinned a PivotTable to a Power BI dashboard using Power BI Publisher for Excel. You have subsequently updated the PivotTable. How is the Power BI dashboard updated?
Click update from the Pin Manager
You create a PivotTable using implicit measures from data in the data model. You select the Summarise value field by setting as Count. You proceed to open the data model and change the Summarize By of the same column to Sum. You return to the PivotTable in Excel and click Refresh. What summarisation is used for the values in the column?
Count Explanation: Summarisation selected on PivotTables override the summarise by selected in the data model. This is true even if the summarise by is performed first on the PivotTable and subsequently in the data model. This means that the summarise by selected in the PivotTable is the result.
What is the best way to relate two fact tables?
Create a one-to-many relationship between reach of the fact tables and a common attributes table. Explanation: One-to-many relationships between each fact table and a common attributes (AKA lookup) table is the best way. Merging or appending tables will not work in most cases as different fact tables usually contain different kinds of data, e.g. sales and returns. The RELATED and RELATEDTABLE DAX functions only work when relationships are already established.
You use Get Data to connect to a .csv file containing product information including product cost, product selling and margin percentage columns. You notice the margin percentage column data type is Decimal Number. You change the data type to Percentage and use Close & Load to load the data into the Excel Data Model. In Excel you click on Manage Data Model and click on the Margin percentage column. What is the data type of the column?
Decimal Number Explanation: The percentage data type in Query Editor (Get Data) is converted to decimal number once you load the data.
In Power Query you select a column of dates and from the Add Column tab you choose Date, then Age. What entries does the newly created column contain and how is the column formatted?
Duration between the current system date/time and the date/time in the selected column. dddd.HH.mm.ss
You receive an Excel file from a colleague. You open the file and from the Power Pivot tab, you select Manage. You see a table containing product-related information with several columns. You notice that some of the rows contain typos and the table is not properly structured. What is the best option for correcting the errors?
Edit the queries from the Queries and Connections pane in Excel Explanation: Edit the queries from the Queries and Connections pane in Excel is the best answer in this scenario. One can also ask for the source data to be amended, but this might not be possible or might take a long time to complete. The purpose of Power Query (edit the queries from Excel) is to cleanse and transform source data - this is what the question requires when it refers to correcting typos and structure. One might use the replace values tool in Power Query, but this is not the best answer because it will only address the typos and not the structure problem. Manually correcting the errors is not possible since the data in the data model is read-only. One can also not manually correct data (like one can do in an Excel table or range) in Power Query. The best option is to use the tools in Power Query to cleanse, transform and load the data back into the data model.
You have multiple tables containing several different sets of attributes for a list of unique products (ProductID). You use Get Data to edit them in PowerQuery. What course of action is optimal for data preparation?
Ensure that the values for ProductID matches for each attribute table, then close and load all of the attribute tables.
Which of the options is correct when you write a DAX measure and use it in a PivotTable?
Explicit measure
Which of the following examples of interaction between Excel and Power BI is false?
File-import Power BI Service dataset from Excel: Connect to and analyze a Power BI Service Dataset from Excel Explanation: All Excel/Power BI Service interaction models is correct except of importing a data model into Excel. Excel can indeed connect to a dataset published in Power BI Service, but this is done using the Get Data feature in Excel, not through File-import.
You are a data analyst that have been tasked to create a report in Excel. The business has indicated that it is critical that the report are always as up to date as possible. Your colleagues in the data analysis team have created a SQL Server Analysis Services server in Azure and have prepared the data for your use. Which of the following options do you choose for connecting to the data to create your report?
Get Data, From Database, From Analysis Services
You are researching information online and discover an article on Wikipedia that contains a table of figures that you can use in an analysis. Which option should you choose in Excel?
Get Data, From Other Sources, From Web
You're analysing a dataset in Excel that have been imported to the data model via Power Query. One of the columns in your dataset contains product prices. You use the field in a PivotTable but the values are displayed as a simple decimal number. You change the number format for the column to Currency, but realise that you have to do the same for every PivotTable that uses the field. You decide to make this change in the data model so that all PivotTables will show the number as currency. Which of the following is the best course of action?
Open the Power Pivot data model, select the relevant column, from the Home tab, change the FORMAT to Currency
Which of the available Excel visualization options can be used to interact with data in the Excel data model?
PivotChart, Pivot Table, CUBE functions
You have created a PivotTable that analyses data contained in the data model. You add fields to the columns, rows and values field wells. You want to show a subtotal below each group of row-level items. Which of the following actions produce the desired effect in a single step?
PivotTable Tools, Design, Subtotals, Show subtotals at bottom of group
You create a PivotTable in Excel as part of analysing data that you added to the Excel data model. You require the month names to be arranged in chronological order. What Power Pivot feature can you achieve the desired result?
Power Pivot, select column, select Sort by Column from Home tab, specify month number column
Use the DAX SWITCH function to add a calculated column named Price Point to the Products table: If Price > 500 then "High" If Price > 300 then "Mid" If Price <= 300 then "Low"
Price Point = Switch ( TRUE (), Products[Price] > 500, "High", Products[Price] > 300, "Mid", Products[Price] <= 300, "Low" )
What technique is being used when Power Query converts transformations written in the M query language to SQL?
Query Folding
How is query folding enabled?
Query folding is automatically applied if supported by the data source
Give the syntax of the SUMX function.
SUMX(<table>,<expression>)
Which of the following DAX formulas require row-context?
SUMX, FILTER
Which chart type is the best to use when multiple observations of two data variables must be analysed?
Scatter
You are building a report in Excel and have added several PivotCharts to the report. You add interactivity for users by adding slicers to the report. However, you notice that manipulating one of the slicers only has an effect on one of the PivotCharts. What do you need to configure so that the slicer will interact with all the PivotCharts on the report?
Select Report Connections from the Sliver tools / options tab for the slicer.
How can Power Query be used to remove certain rows from the data being imported?
Select the drop-down menu on the column heading. explanation: The filter data feature is used to remove certain rows from the data being imported and is accessed through the drop-down menu in the column header. Using Group By from the Transform tab effectively reduces the number of rows being imported but not by removing them but rather by aggregating one or more columns. There is no filter rows or filter data buttons on the transform tab. Parse is used to interpret XML or JSON data and not to filter data.
Which DAX time intelligence function can be used to evaluate the year-to-date value of the expression in the current context?
TOTALYTD
Which is not an available chart type in Excel?
Table Available chart types include: - sunburst - histogram - radar - 100% stacked area
Consider the following data information Table Name: Sales Column Name: Amount Data type: Whole Number Sample value: 5678 Consider the following measure My Column := FORMAT(Sales[Amount], "$#,##0;($#,##0)") What is the data type of the new column?
Text Explanation: The resulting data type of the FORMAT function is always text regardless of the specified string format argument. The results of the column measure in the question will look like currency - for example the sample amount will be converted to $5,678, but the actual data format of the new column will be text.
In Power Query, you select a column containing dates from a table containing other columns. From the Transform tab you choose Date, then select Earliest. What happens?
The table is replaced with a single value showing the earliest date from the column that aws selected Explanation: The earliest and latest date functions replaces the entire table with the earliest or latest date for the selected column. This works in the same way as the statistical functions and is meant to be used for data exploration. The step is almost always removed from the applied steps list after confirming the result.
You have created a report in Excel using PivotTables with conditional formatting and PivotCharts. You want to make it easy for users to manipulate all the elements of your report by selecting items on a control. Which of the following objects can provide this capability? Choose 2.
Timeline, Slicer
By default, where are subtotals displayed when switching a PivotTable layout to Compact Form?
Top of Group
By default, where are subtotals displayed when switching a PivotTable layout to Outline Form?
Top of Group
You are connecting to a CSV file as a data source and need to ensure that the data is in the correct format. You use Get Data, From File, From Text/CSV. You navigate the the file on your local hard drive and select open. You are presented with the data preview as in the exhibit. Which option do you select to load the data into the Excel data model?
Transform Data
Which term best describes the process of transforming a table from a horizontal orientation to a vertical orientation by turning distinct column values into rows?
Unpivot
Which of the following is false regarding model optimisation in Excel?
Use DAX Columns Using DAX measures instead of calculated columns is more optimal - this option is therefore false. The workbook size optimizer tool is an external tool used as part of model optimization. Pre-aggregating data also known as binning data is a common model optimisation technique. Using the appropriate data type can significantly optimize the data model - for example, removing the time element from a date/time field is often used if the time element is not applicable for the analysis. Bringing in only the data that is actually required for the analysis is the most effective way to optimise the model.
You create a PivotTable and drag a field containing dates from an expanded calendar table to the rows field well. The Calendar table has the dates, months, years and every other date breakdown as separate columns. Predictably, all the dates in the dataset are displayed on the rows of the PivotTable. However, you want the PivotTable to show data by month and not for each individual date. What is the best way to accomplish your task?
Use the Group Field in the PivotTable Tools / Analyze tab
You are analysing data in an Excel data model and need to visualise the data using a Treemap. However, you discover that a treemap is not selectable as a PivotChart. What is the best course of action?
Use the OLAP Tools to conver the PivotTable to CUBE formulas Explanation: The best course of action is to convert the PivotTable to CUBE formulas and to insert a normal Treemap (not a PivotChart) using the formulas as the source. Inserting a Treemap using the PivotTable as the source will also work, but is not the best answer as changes to the source data might change the structure of the PivotTable that might make the Treemap display incorrect results. Once a PivotTable is converted to formulas, the structure of the table doesn't change, but the values displayed will change as the data in the data model is updated. Giving up on your dreams is not recommended, and therefore not the right answer. The question asks for a Treemap to be used, using other types of charts is not the best answer in this case.
Under normal circumstances it is recommended to always use fully qualified column references. Under what circumstances is it permissible and safe to use naked column references instead?
When creating a DAX calculated column.
When would you use the DAX CALCULATE function (Function 1) instead of a simple aggregation function like SUM (Function 2)? Function 1 := CALCULATE(SUM(Sales[Amount])) Function 2 := SUM(Sales[Amount])
When you want to apply a filter context in the formula.
In Power Pivot you connect to a SQL Server database and import data into the data model. Which of the following data optimization steps is not possible?
all the options below are possible - choose the correct data types for columns - rename tables and columns to intuitive names - remove unneeded columns - add descriptions to tables and columns - hide columns and tables required in measures, but not needed in the analysis (Answer is: none of these)
What do you need to configure in order to edit the data in the Excel data model?
none of the options below - updatable: on - enable fast load: on - enable write mode: on
You have two tables as in the exhibit loaded into the Excel data model. In the diagram view of the data model, how would you establish a relationship between the tables?
none of the options noted below would help: - use the create relationship tool from the design tab and specify the customer ID field for both tables - drag the customer ID field from the Name table (primary key) to the Age table (foreign key) - Drag the Customer ID field from the Age table (foreign key) to the Name table (primary key)
