Proper study guides for Regenerate Microsoft Analyzing and Visualizing Data with Microsoft Excel (beta) certified begins with Microsoft 70-779 preparation products which designed to deliver the Breathing 70-779 questions by making you pass the 70-779 test at your first time. Try the free 70-779 demo right now.
Check 70-779 free dumps before getting the full version:
NEW QUESTION 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 2021
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?
- A. =CUBEMEMBER(“ThisWorkbookDataModel”, “[DailyRepairs]. [Avg Revenue Per Hour]”,CUBEMEMBER (“ThisWorkbookDataModel”, “[Dimensions]. [Repair Type]. [Engine]”))
- B. =CUBEVALUE(“ThisWorkbookDataModel”, “[Measures]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[Dimensions]. [Repair Type]. [Engine]”))
- C. =CUBEMEMBER(“ThisWorkbookDataModel”, “[DailyRepairs]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[RepairTypes]. [Repair Type]. [Engine]”))
- D. =CUBEVALUE(“ThisWorkbookDataModel”, “[Measures]. [Avg Revenue Per Hour]”, CUBEMEMBER (“ThisWorkbookDataModel”, “[RepairTypes]. [Repair Type]. [Engine]”))
Answer: B
Explanation:
References:
https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a-84a8f00dcbe0 https://www.tutorialspoint.com/advanced_excel_functions/advanced_excel_cube_cubemember_function.htm
NEW QUESTION 2
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 worksheet. The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
Your company has 100 product subcategories and more than 10,000 products.
End of repeated scenario.
You need to create a measure named [Sales Monthly RT] that calculates a running total of [Sales] for each date within a month as shown in the following exhibit.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation:
SUM([Sales],DATESMTD(DimDate[Date])) CALCULATE(SUM([Sales],DATESMTD(DimDate[Date])))
NEW QUESTION 3
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?
- A. From powerbt.com, select the repor
- B. From the File menu, click Save as.
- C. From Excel, create a new query that uses the Data Catalog.
- D. From powerbi.com, select the report From the File menu, click Download report.
- E. From powerbi.com, select the report and click Analyze in Excel.
Answer: D
NEW QUESTION 4
Your company has sales offices in several cities.
You create a table that represents the amount of sales in each city by month as shown in the exhibit.
You need to ensure that all values lower than 250 display a red icon. The solution must ensure that all values greater than 500 display a green icon.
Solution: You create a measure, and then define a target value. Does this meet the goal?
- A. Yes
- B. No
Answer: B
NEW QUESTION 5
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.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation:
Click From CSV, and then select the first file in the folder. Edit the query, and then click Append Queries.
From the Power Pivot tab, click Add to Data Model.
NEW QUESTION 6
You need to configure a PivotChart as shown in the following exhibit.
Witch chart element should you enable?
- A. Data Labels
- B. Axis Titles
- C. Data Table
- D. Error Bars
Answer: B
NEW QUESTION 7
You have a table named Sales that has three columns named Region, Country, and SalesAmount. You create a PivotTable as shown in the following exhibit.
You need to ensure that the PivotTable appears in three columns as shown in the following exhibit.
What should you do?
- A. On the Design tab, click Report Layout and then click Show in Compact Form.
- B. Move Country from the Rows area to the Columns area.
- C. Move Country from the Rows area to the Values area.
- D. On the Design tab, click Report Layout and then click Show in Tabular Form.
Answer: D
NEW QUESTION 8
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?
- A. From the Power Pivot model, edit the synonyms.
- B. From PowerPivot Settings, modify the language options.
- C. From PowerPivot Settings modify the categorization options.
- D. From Workbook Connections, add a connection.
Answer: B
NEW QUESTION 9
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?
- A. From the Power BI tab in Excel, click Pin.
- B. From the File menu in Excel, click Publish.
- C. From powerbi.com, upload the excel workbook.
- D. From powerbi.com, click Get apps.
Answer: A
NEW QUESTION 10
You open an Excel worksheet as shown in the following exhibit.
You need to export the data into a dataset in the Microsoft Power BI service. What should you do first?
- A. Save the tile as an Excel template.
- B. Select the data, and then insert a table.
- C. Select the data, and Then insert a PivotTable.
- D. Install Power BI Publisher for Excel.
Answer: D
NEW QUESTION 11
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.
Answer: C
Explanation:
https://support.office.com/en-us/article/data-types-in-data-models-e2388f62-6122-4e2b-bcad-053e3da9ba90?ui=
NEW QUESTION 12
You have a query that retrieves customers and their locations. You have a sample of the data as shown in the following table.
Additional customers and locations are added frequently.
You need to transform the data as shown in the following table.
What should you do?
- A. Select the Locations columns and then select Split Column by Delimite
- B. Use a comma as the delimiter and split into rows.
- C. Select the Locations columns and then select Split Column by Delimite
- D. Use a comma as the delimiter and split into columns.
- E. Select the Customer columns, and then click Unpivot Columns.
- F. Select the Customer columns, and then click Unpivot Other Columns.
Answer: A
NEW QUESTION 13
Note: This question is part of a series of questions that use the same scenario, For your convenience 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.
The data model is shown in the Data Model exhibit.
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 are two measures defined in DailyRepairs. Total Hours sums the Hours column, and Total Revenue sums the Revenue column.
Workshops has 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.
Repair types has a list of all the repair types. Dates has a list of all the repair types.
Dates has a list of dates from 2015to 2021. End of repeated scenario.
You need to create a PivotChart that displays the month, the hours of the month, and the hours of the previous month, as shown in the following exhibit.
Which DAX formula should you use for the Total Hours Last Month measure? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation:
IF (ISBLANK([Total Hours]), BLANK(), CALCUATE [Total Hours], DATESADD (tblDates[Date]),
-1,MONTH)))
NEW QUESTION 14
From a workbook query, you import a table that has the following data.
You need to configure the table to appear as shown in the following table.
What should you do?
- A. From the Format menu, click Trim.
- B. From the Format menu, click Clean.
- C. From the Split Column menu, click By Delimiter.
- D. From the Extract menu, click Last Characters.
Answer: A
NEW QUESTION 15
You create a new workbook and add a table to a data model. The data is shown in the following table.
You need to create a visualization as shown in the following exhibit.
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.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation:
Create a Pivot Table. Create a measure.
Create a Power View Report
NEW QUESTION 16
You have the following tables in a data model.
You create a PivotTable to display SaleAmount by Month. A sample of the results are shown in the following table.
- A. In the data model, modify the Sort By Column setting for Date[Month Year].
- B. From PivotTable Fields, add Date[Year] to the Rows area.
- C. In the data model, modify the Sort by Column setting for Sales[Date].
- D. From PivotTable Fields, modify the Field Settings for DatelMonth Year].
Answer: C
NEW QUESTION 17
You have two visualizations as shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation: 
NEW QUESTION 18
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 2021
End of repeated scenario.
You need to create a PivotChart that displays the month, the hours of the month, and the hours of the previous month, as shown in the following exhibit.
Which DAX formula should you use for the Total Hours Last Month measure? To answer, drag the appropriate fields to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation:
IF (ISBLANK[(Total Hours]),BLANK(), CALCULATE([total Hours], DATEADD(tblDates(Date),
-1,MONTH)))
NEW QUESTION 19
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 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 2021
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?
- A. In the Power Pivot model, change the data type for Workshop[ID] to General
- B. In the workbook query for Workshops, add an index column
- C. In the Power Pivot model, change the Table Behavior setting for Workshops
- D. In the workbook query for Workshops, filter [IsLatest] to equal 1
Answer: C
Explanation:
References: https://msdn.microsoft.com/en-us/library/hh560544(v=sql.110).aspx
NEW QUESTION 20
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?
- A. All the columns except InternalPrice will load to the worksheet The values in ResellerPrice will be correct.
- B. An error message will appear and all the data will fail to load.
- C. An error message will appear and all the columns except InternalPnce and ResellerPrice will load to the worksheet.
- D. All the columns except InternalPrice will load to the workshee
- E. The values in ResellerPrice will be null.
Answer: D
NEW QUESTION 21
You have the PivotTable shown in the following exhibit.
You need to display only rows in the PivotTable in which the sum of LineTotal is greater than 100. What should you do?
- A. From Row Label, configure a Label filter.
- B. Add a slicer for LineTotal and select the values from the slicer.
- C. From Row Label, configure a Value Filter.
- D. Add LineTotal to the Filters area of PivotTable Field
- E. Configure the Filters value.
Answer: B
NEW QUESTION 22
You use a workbook query to import a table named Customers that contains a column named CustomerName. CustomerName has names in the format of Lastname, Firstname.
You need the CustomerName column to contain names in the format of Firstname Lastname. A space must separate Firstname and Lastname.
Which two commands should you use? To answer, drag the appropriate fields to the correct areas. Each field may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation: 
Split Column By Delimiter Merge Columns
https://support.office.com/en-us/article/split-a-column-of-text-power-query-5282d425-6dd 0-46ca-95bf-8e0da9539662
https://support.office.com/en-us/article/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9f0
NEW QUESTION 23
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.
The data model is shown in the Data Model exhibit.
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 are two measures defined in DailyRepairs. Total Hours sums the Hours column, and Total Revenue sums the Revenue column.
Workshops has 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 alt the repair types. Dates has a list of dates from 2015 to 2021. End of repeated scenario.
You plan to analyze The average revenue per hour by combinations of day, repair type, and workshop name. You need to create a measure to support the planned analysis.
Which DAX formula should you use? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation:
DIVIDE ([Total Revenue ],[ Total Hours], BLANK())
NEW QUESTION 24
You have a query as shown in the following exhibit.
You need to ensure that the data only contains rows that have a valid date.
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.
- A. Mastered
- B. Not Mastered
Answer: A
Explanation:
Change the column type of the Date Column Group rows by the Date column
Remove errors from the date column.
NEW QUESTION 25
......
Thanks for reading the newest 70-779 exam dumps! We recommend you to try the PREMIUM Dumpscollection 70-779 dumps in VCE and PDF here: http://www.dumpscollection.net/dumps/70-779/ (117 Q&As Dumps)