Week 1

Question 1

Easy Q1: You plan to get data for a Power BI dataset from flat files. You need a location type that requires you to provide credentials for a Microsoft account. Which location type should you recommend?

Answer 1

OneDrive Personal Account - INCORRECT

Question 2

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

Answer 2

Local file

Question 3

Easy Q3: You have multiple Excel files stored in a folder synced with One Drive 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?

Answer 3

SharePoint folder

Question 4

Easy Q4: From Power BI Desktop, you create a data source by importing an Excel file that contains 10,000 rows. You plan to identify data anomalies within the data source. Which interface should you use to display the column distribution and column quality graphs?

Answer 4

The Power Query Editor

Question 5

Easy Q5: 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?

Answer 5

Advanced Editor in Power Query Editor

Question 6

Easy Q6: 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 one containing year, and the third containing sales amount for that month and year. Which transformation should you use first?

Answer 6

Unpivot

Question 7

Easy Q7: You have a matrix that has a column named Country and columns for each month of sales data. The names of the sales data columns use a format of Month Year, for example: January 2022. The month column contains the Sales Amount values for each month. You need to reshape the table to have all sales amount values in a single column. A new column should also be added and contain the relevant month year for each row of data. What should you do from the Power Query Editor?

Answer 7

Select the Country column and then select Unpivot Other Columns

Question 8

Easy Q8: From Power BI Desktop, you create a data source by importing an Excel file that contains 10,000 rows. You plan to identify data anomalies within the data source. You need to ensure that column distribution considers all rows in the Excel file. What should you do?

Answer 8

In the Power Query Editor window, modify the profiling status

Question 9

Easy Q9: You have to find the percentage of transactions performed by non-unique customers. Which data preview tool in Power Query is the most suitable choice?

Answer 9

Column profile

Question 10

Easy Q10: You have to find the percentage of transactions with empty Product ID values. Which data preview tool in Power Query is the most suitable choice?

Answer 10

Column quality

Question 11

Med Q1: You create a Power BI report using an Excel workbook stored on OneDrive for Business as the data source in Power BI Desktop. You need to replace the Excel workbook with a new version that contains updated data. You should NOT change the report data model after replacing the workbook.

Answer 11

The Excel workbook can be edited in Power BI
False

Question 12

Med Q2: You are analyzing data quality for a dataset in Power Bl.
You use Power Query to preview the data. You need to examine the quality of the data.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer 12

Statement Using Column profile in Data Preview, you can gain an insight into only the first 1,000 rows in the dataset.
False

Question 13

Med Q3: 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 the 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?

Answer 13

From Power Query Editor, update the current changed type step and replace convert from number to text for the ZIP column

Question 14

Med Q4: You have a table that contains a column named ProductID The ProductID column contains values that have a prefix of PD followed by five numbers, for example: PD15332
You plan to convert the ProductID values to numbers(integers). You need to remove the leading two letters from the current ProductID values.
Which command should you use?

Answer 14

Replace values

Question 15

Med Q5: When importing data from an Excel workbook into Power BI, you receive the error message: “We couldn’t find any data formatted as a table”.

Answer 15

What should you do to resolve the error?
In the Excel workbook, select the data you want to import, create a table and save the change

Question 16

Med Q6: You are a business intelligence developer.
You need to use data contained in Azure Analysis Services to build your report.
You need to connect this data source. What two solutions can you use to achieve this goal?
Each correct answer presents a complete solution?

Answer 16

Import

Question 17

Med Q7: You are connecting to data that is stored in your organisation’s Common Data Service.
You retrieve the Instance url from your Power Apps environment as shown below:
https://org077bbf3e.crm4.dynamics.com/
How would you configure the Dataverse connector in Power BI to connect to this environment?

Answer 17

https://org077bbf3e.crm4.dynamics.com/

org077bbf3e.crm4.dynamics.com

Question 18

Med Q8:You have a query named FactSales that retrieves data from a SQL Server table. You need to update the query to prevent new columns that may be added to the table in the future from getting imported during dataset refreshes.
Which two applied steps can you use in Power Query Editor to achieve the goal? Each correct answer presents a complete solution.

Answer 18

Choose Columns
Remove other columns

Question 19

Med Q9: You create a Power BI report using an Excel workbook stored on OneDrive for Business as the data source in Power BI Desktop. You need to replace the Excel workbook with a new version that contains updated data. You should NOT change the report data model after replacing the workbook.
Identify if the following statement is True or False

Answer 19

The Excel workbook must have the same file path and file name as the original workbook
False

Question 20

Med Q10: You create a Power BI report using an Excel workbook stored on OneDrive for Business as the data source in Power BI Desktop. You need to replace the Excel workbook with a new version that contains updated data. You should NOT change the report data model after replacing the workbook.
Identify if the following statement is True or False

Answer 20

The Excel workbook must have the same structure as the original workbook
True

Question 21

Hard Q1: You have an Azure SQL database that contains the 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:
1. There is a row for every sales order detail row in the resulting table, even if there is no corresponding sales order row
2. 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.
3. 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?

Answer 21

Merge the tables using the left outer join kind based on the sales order id

Question 22

Hard Q2: You have a Power BI data source that contains the following tables: 1. Category: Lists the product category 2. Subcategory: Lists the product subcategory 3. ProductName: Lists the name of the product You need to optimize the star schema model for the Power BI dataset. How should the tables be imported into the model?

Answer 22

Use the Combine command to create a single loaded table for product

Question 23

Hard Q3: You have an Excel spreadsheet that contains two columns as follows:
1. Category: Contains names of categories
2. 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:
1. Have multiple columns, where one column represents one category
2. Have a single row that includes the total count of subcategories for each category
Which transformation should you use first?

Answer 23

Pivot columns

Question 24

Hard Q4: You plan to publish a dataset from Power BI Desktop. You need to ensure that a server name can be changed after the dataset has been published to the Power BI service Which two actions should you perform? Each correct answer presents a part of the solution.

Answer 24

Create a parameter
From the Data Source settings, update the server source to use a parameter

Question 25

Hard Q5: 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.
What should you do to resolve the error?

Answer 25

Editing permissions
Modifying the file path
Clearing permissions

Question 26

Hard Q6: You have created a Power BI dataset and need to create multiple reports. What is a benefit of using a Power BI dataset as a data source for multiple reports compared to each report having a separate dataset

Answer 26

It reduces the number of datasets, refreshes and storage requirements in the Power BI service

Question 27

Q7: You have a Power BI model with the following fact tables and storage modes
1. FactStoreSales (Import mode)
2. 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?

Answer 27

Dual

Question 28

Hard Q8:You have a list of customers in an Excel spreadsheet and a Sales table in on-premises SQL Server. You need to combine the two into a single table of only the customers who have not purchased any product. Finally, load this data into Power BI. Ensure that you minimize the size of the data model.
How do you combine the Customer & the Sales table?

Answer 28

Merge Queries as New

Question 29

Hard Q9: You use Power BI Desktop to prepare data.
You examine data quality in Power Query. You need to discover anomalies in the data.
Which two options should you use? Each correct answer presents a complete solution.

Answer 29

Column Distribution
Column Profile

Question 30

Hard Q10: Which tool should you use in Power Query to discover and investigate and improve performance issues?

Answer 30

Query diagnostics