What does this exam test?

Data analysis and visualization in Excel.

You have a data model in Excel. You export the data and the data model into a dataset in the Microsoft Power BI service. What can you use to modify the data model that is published to the Power BI service?

Correct Answer: Microsoft Power BI Desktop

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. Start of repeated scenario. You have six workbook queries that each extracts a table from a Microsoft Azure SQL database. The tables are loaded to the data model, but the data is not loaded to any worksheets. The data model is shown in the Data Model exhibit. (Click the Exhibit button.) Exhibit: Your company has 100 product subcategories and more than 10,000 products. End of repeated scenario. You need to create a simplified view of the workbook for some users. The simplified view must only display data from FactSales, DimProduct, and DimDate. What should you do in the data model?

Correct Answer: Modify the Table behavior settings for FactSales, DimProduct, and DimDate

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. Start of repeated scenario. You have six workbook queries that each extracts a table from a Microsoft Azure SQL database. The tables are loaded to the data model, but the data is not loaded to any worksheets. The data model is shown in the Data Model exhibit. (Click the Exhibit button.) Exhibit: Your company has 100 product subcategories and more than 10,000 products. End of repeated scenario. You have a PivotChart that uses Manufacturer as the axis and the sum of SalesAmount as the values. You need to ensure that only the top 10 manufacturers appear in the chart. What should you do?

Correct Answer: Create a calculated column

You have a KPI named Goal that calculates the sales from the previous year and multiplies the sales by 1.1. You need to modify Goal to multiply the sales from the previous year by 1.15. What should you do?

Correct Answer: From the properties of the KPI, modify the KPI base field

You have an Excel spreadsheet that contains a PivotChart. You install Microsoft Power BI Publisher for Excel. You need to add a tile for the PivotChart to a Power BI dashboard. What should you do?

Correct Answer: From the File menu in Excel, click Publish

You have an Excel workbook that contains two tables named User and Activity. You plan to publish the workbook to the Power BI service. Users will use Q&A in the Power BI service to perform natural language queries. You need to ensure that the users can query the term employee and receive results from the User table. What should you do before you publish to Power BI?

Correct Answer: From PowerPivot Settings, modify the Language options

You open C:\Data\Data.xlsx in Excel. When you attempt to publish the file to Microsoft Power BI, you receive the following error message: "We couldn't publish to Power BI. Make sure your workbook is saved as an Excel file (.xlsx or .xlsm) and is not password protected." You need to ensure that you can publish the file to Power BI. What should you do first?

Correct Answer: Decrypt the workbook

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. Start of repeated scenario. You are creating reports for a car repair company. You have four datasets in Excel spreadsheets. Four workbook queries load the datasets to a data model. A sample of the data is shown in the Data Sample exhibit. (Click the Exhibit button.) Data Sample exhibit: The data model is shown in the Data Model exhibit. (Click the Exhibit button.) The tables in the model contain the following data: ✑ DailyRepairs has a log of hours and revenue for each day, workshop, and repair type. Every day, a log entry is created for each workshop, even if no hours or revenue are recorded for that day. Total Hours and Total Revenue column. ✑ Workshops have a list of all the workshops and the current and previous workshop managers. The format of the Workshop Manager column is always Firstname Lastname. A value of 1 in the IsLatest column indicates that the workshop manager listed in the record is the current workshop manager. ✑ RepairTypes has a list of all the repair types ✑ Dates has a list of dates from 2015 to 2018 End of repeated scenario. When you attempt to create a relationship between DailyRepairs and Workshops, Power Pivot generates the following error message: "The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values". You need to ensure that you can create a valid relationship between the tables. What should you do?

Correct Answer: In the workbook query for Workshops, filter [IsLatest] to equal 1

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. Start of repeated scenario. You are creating reports for a car repair company. You have four datasets in Excel spreadsheets. Four workbook queries load the datasets to a data model. A sample of the data is shown in the Data Sample exhibit. (Click the Exhibit button.) Data Sample exhibit: The data model is shown in the Data Model exhibit. (Click the Exhibit button.) The tables in the model contain the following data: ✑ DailyRepairs has a log of hours and revenue for each day, workshop, and repair type. Every day, a log entry is created for each workshop, even if no hours or revenue are recorded for that day. Total Hours and Total Revenue column. ✑ Workshops have a list of all the workshops and the current and previous workshop managers. The format of the Workshop Manager column is always Firstname Lastname. A value of 1 in the IsLatest column indicates that the workshop manager listed in the record is the current workshop manager. ✑ RepairTypes has a list of all the repair types ✑ Dates has a list of dates from 2015 to 2018 End of repeated scenario. You create a measure named Average Revenue Per Hour that calculates the average revenue per hour. You need to populate a cell in a worksheet to display the Average Revenue Per Hour where Repair Type is Engine. Which Excel formula should you use?

Correct Answer: =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[Dimensions]. [Repair Type]. [Engine]"))

You have the Excel worksheet shown in the exhibit. (Click the Exhibit button.) Exhibit: You need to transform the data by using Query Editor. What should you do first?

Correct Answer: From the Data tab, click From Table/Range

You have 20 workbook queries that load 20 CSV files to a local computer. You plan to send the workbook and the 20 CSV files to several users. The users will store the files in various locations. You need to ensure that the users can change the path to the CSV files in the queries as quickly as possible. What should you do from Query Editor?

Correct Answer: For each query, create a new query that uses a reference. Modify the source of each new query

You have multiple workbook queries that load data from tables in Microsoft Azure SQL Database to a Power Pivot data model. You discover that new rows were added to the tables in Azure SQL Database. You need to ensure that the workbook has the new data. What should you do?

Correct Answer: From the Data tab, click Refresh All

You create an Excel workbook named SalesResults.xlsx. You create a workbook query that connects to a Microsoft SQL Server database and loads data to the data model. You create a PivotTable and a PivotChart. You plan to share SalesResult.xlsx to several users outside of your organization. You need to ensure that the users can see the PivotTable and the PivotChart when they open the file. The data in the model must be removed. What should you do?

Correct Answer: Modify the source of the query

You have an Excel workbook query that loads data to a worksheet and the data model. You need to ensure that the data is refreshed whenever you open the workbook. What should you do?

Correct Answer: From the Power Pivot model, modify the Table Behavior setting

You have a workbook query that loads the following table You pivot the table on the Key column by using Value as the values column, and you receive the results shown in the following table. You need to ensure that the data appears as shown in the following table. What should you do?

Correct Answer: Delete the Pivoted Column step. Select the Key column, and then click Unpivot Columns

You need to create a PivotChart that has a filter as shown in the following exhibit. What should you do first?

Correct Answer: From Query Editor, create a function

You have the following table. You plan to use [Month Name] as the axis in a PivotChart. You need to ensure that whenever [Month Name] is used in a chart, the months are displayed chronologically by default. What should you do?

Correct Answer: Add a calculated column named [ID] that uses the [Month Name]&[Month Number] DAX formula

You have the data model shown in the Data Model exhibit. (Click the Exhibit button.) Data Model exhibit: You have the PivotChart shown in the Pivot Chart exhibit. (Click the Exhibit button.) Pivot Chart exhibit: You need to change the current view of the PivotChart to display ProductCategory only. What should you do?

Correct Answer: Right-click the PivotChart and click Reset to Match Style

You have a table in a Power Pivot model that is loaded from a Microsoft SQL Server database. The source table has four columns named ID, Price, Quantity, and Total. Total is derived by multiplying Price and Quantity. ID is a unique row identifier. You need to minimize the amount of memory used to load the model. The solution must ensure that you can create visualizations based on Price, Quantity, and Total. What should you do?

Correct Answer: Replace the Total column by using a measure

You add two tables named Date and Invoices to a data model. Invoices contains a column named InvoiceDate that has a data Type of Date. Date contains a column named DateID that has a Data Type of Whole Number. DateID is in the format of YYYYMMDD. You need to create a relationship between Date and Invoices. What should you do first?

Correct Answer: Create a calculated column in Invoices that uses the FORMAT DAX function

What does this exam test?

Data analysis and visualization in Excel.

Welcome to awslagi.com – Sharing AWS, Google Cloud, Microsoft Azure IT Exams. It's free for all!

Analyzing and Visualizing Data with Microsoft Excel

Analyze and visualize data with Microsoft Excel professionally.

4.8★★★★★(311 ratings)

19,525 studentsIntermediate

Develop strong Excel-based analysis skills for organizing, interpreting, and presenting data. The exam covers formulas, charts, data handling, and visualization techniques that support business reporting. This certification is useful for professionals who rely on Excel as a core analysis tool. It helps you deliver clearer insights without needing specialized BI platforms.

What you'll learn

Use Excel formulas- Analyze datasets- Build charts and visuals- Present data clearly- Support business reporting

70-779

Lectures1

Quizzes0

Duration30 week

Students19,525

$20.00

📢 Unlock Full Access to Success!

"The content displayed here is a promotional preview and contains only 50% of the actual exam database. To access the complete set of updated questions, please upgrade to the Full Version."

👉 Click the button and follow the instructions to secure your full access immediately!