70-779 Analyzing and Visualizing Data with Microsoft Excel exam is a hot Microsoft certification exam, Exam4Training offers you the latest free online 70-779 dumps to practice. You can get online training in the following questions, all these questions are verified by Microsoft experts. If this exam changed, we will share new update questions.
Certification Provider: Microsoft Exam Name: Analyzing and Visualizing Data with Microsoft Excel Exam Code: 70-779 Official Exam Time: 120 mins Number of questions in the Official Exam: 40-60 Q&As Latest update time in our database: September 27,2023 70-779 Official Exam Topics:
Topic1 : Consume and Transform Data by Using Microsoft Excel
Topic2 : Import from data sources / Connect to and import from databases, files, and folders; connect to Microsoft SQL Azure, Big Data, SQL Server Analysis Services (SSAS), and Power Query; import supported file types; import from other Excel workbooks; link to data from other sources
Topic3 : Perform data transformations / Design and implement basic and advanced transformations, apply business rules, change data format to support visualization, filter data, format data
Topic4 : Manage incomplete data; handle data received as a report; remove unnecessary rows, columns, and data; remove repeating headers / Create and optimize data models
Topic5 : Manage data relationships, optimize models for reporting, manually enter data, perform Get & Transform, create automatic relationships, create manual relationships / Create calculated columns, measures, and tables
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 chokes, 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.
Data Sample:
The data model is shown in the Data Model exhibit.
Data Model:
A . In the workbook query for Workshops, filter [IsLatest] to equal 1. B . In the workbook query for Workshops, add an index column. C . In the Power Pivot model, change the data type for Workshop[lD] to General. D . In the Power Pivot model, change the Table Behavior setting for Workshops.
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 which Number. DateID is in the format of YYYYMMDD.
You need to create a relationship between Date and Invoices.
What should you do first? A . Change the Data Type of InvoiceDate and DateID to Text. B . Create a measure in Invoices that uses the Format DAX Function. C . Change the Data Type of DateID to Date. D . Create a calculated column in Invoices that uses the Format DAX function.
You have a data model that contains a table named SalesOrders has four columns named Orderld, SalesAmount, OrderDate, and Territory.
You plan to create a PivotChart that will display the percentage of SalesAmount for each Territory.
You need to create a measure to calculate the percentage of sales of each territory.
How should you complete the DAX formula? To answer, drag the appropriate value to the correct targets. Each value may be used once, more than once, or not at all. You need to drag the split bar between panes or scroll to view content.
You need the wedges of the pie chart to be separated as shown in the following exhibit.
A . Change the chart type to Pie of Pie. B . Right-click the pie chart, click Expand/Collapse, and then click Expand. C . Right-click the pie chart, click Expand/Collapse, and Then click Expand Entire Field D . Select a wedge of the pie chart and then drag the wedge.
Your network contains a folder that has data files m various formats.
You need to identify how many files of each extension type are in the folder by using Query Editor.
What should you do? A . Create a query that uses a file source, and then use the Count Values command on the Transform tab. B . Create a query that uses a folder source, and then use the Group By command on the Home tab. C . Create a query that uses a file source, and then use the Group By command on the Home tab. D . Create a query that uses a folder source, and then use the Count Values command on the Transform tab.
You have the Excel worksheet shown in the exhibit.
You need to transform the data by using Query Editor.
What should you do first? A . From the Data tab, Click From Table/Range. B . From the Insert tab, Click Store. C . From the Data tab, Click Flash Fill. D . From the Data tab, Click Consolidate.
Answer: A
Explanation:
Excel uses a dedicated Query Editor to facilitate and display data transformations. When you select Data > Get Data , then select the data source, such as a workbook, or a database, the Navigator window appears so you can select which table (or tables) you want to use in your query. When you select a table, a preview of its data is shown in the right pane of the Navigator window.
You have 12 sales reports stored in a folder as CSV files. Each report represents one month of sales data for a year. The reports have the same structure.
You need to analyze the entire year of sales data.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
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.
A . Change the aggregate value function of the pivot. B . Select the ID column, and then click Unpivot Columns C . Change the Data Type of the Value column. D . Delete the Picoted Column step. Select the Key column, and the click UnpivotColumns .
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? A . From Power Pivot, modify the measure. B . From the properties of the KPI, modify the KPI base field. C . From Power Pivot, create a new calculated column, and then modify the KP E . From the properties of the KPI, modify the absolute value.
