70-779 free questions – Analyzing and Visualizing Data with Microsoft Excel

1. You have a workbook query that loads data from a table in a Microsoft Azure SQL database. The table has a column named LineTotal. The following is a sample of the data in LineTotal:

• 40
• 1
• 999
• 7658
• 883432

You need to ensure that when you load the data to the model, LineTotal is set as currency.

What should you do from Query Editor?

2. You have a workbook query that loads data from C:\Data\Users.xlsx.

You move Users.xlsx to a shared folder on the network.

You need to ensure that you can refresh the data from Users.xlsx.

What should you do?

3. You have the Excel worksheet shown in the exhibit.

You need to transform the data by using Query Editor.

What should you do first?

4. You have a workbook query that gets a table from an Excel workbook. The table contains a column1.

In the query, you configure Column1 to use a Data Type of Whole Number.

You refresh the data and find several errors in Column1. You discover that new entries in the table contain nonnumeric characters.

You need to ensure that when the data is imported, any fields that contain nonnumeric values are set 1.

What should you do from Query Editor?

5. You have an Excel workbook that has the following two workbook queries:

A query named Consultants that retrieves a table named Consultants_Contact from a Microsoft SQL Server database

A query named Employees that retrieves a table named Employee_Contact from a Microsoft Azure SQL database

Both tables have the same columns.

You need to combine all the data from Consultants and Employees into one table.

Which command should you use?

6. You have a workbook query that loads data from a table named Products.

Products contains a column named IntemalPrice that has a Data Type of Decimal.

From Query Editor you create a custom column named ResellerPrice that uses a formula to multiply InternalPrice by 1.2, and then you remove the IntemalPrice column.

What will occur when you load the data to a worksheet?

7. 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 SalesResults.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?

8. Your company has a data analyst who uses Microsoft Power BI Desktop to create a data model and several reports.

The data analyst publishes the reports to the Power BI service.

You need to create a PivotTable in Excel that uses the data model created by the data analyst. The solution must prevent the data from being imported into Excel.

What should you do first?

9. You have an Excel workbook that displays two PivotCharts. One chart displays sales by month. The other chart displays sales by year.

You add a slicer for month.

You discover that when you select a month in the slicer, the data in the sales by year PivotChart changes.

You need to prevent the slicer from affecting the sales by year PivotChart.

What should you do?

10. You install Microsoft Power BI Publisher for Excel.

You need to use Excel to connect and analyze Power BI data.

To which two types of Power BI data can you connect? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.