Excel Part 1

10. What is Query Folding?

10. Query folding is when Power Query converts its transformations in the Native query language of the data source.

11. Which two ways can be used to reduce the number of rows in a data set? A. Filter Functions B. Remove Columns C. Parameters D. Extract

11. Answers A and C: Filter Functions and Parameters. Remove columns does not remove rows, and Extract is used to remove portions of text fields.

12. What is the difference between the Extract Power Query Function that exists on the Transform tab versus the Add Column tab?

12. Like many functions, it is important to know the context in which you are using it. If you have chosen a column as the subject of an Extract, know that if you do this from the Add Column tab that a new column with the extracted value is created. If you do this from the Transform tab the column value is replaced with the newly extracted value.

2. The marketing department has asked you to perform some analysis of 2017 sales. They have given you access to their Azure SQL Database which contains 1.2 million rows. What options do you have for importing this data into Excel knowing that they want to analyze all the data?

2. The question said that the data is imported into Excel. Given this, the Excel Data Model is your only option as traditional Excel spreadsheets have a 1,048,576-row limit.

3. In which version of Excel was the Excel Data Model introduced? What was it originally called?

3. The Data Model was first introduced into Excel 2010 as PowerPivot. The two terms are still used interchangeably.

4. How many data models can a workbook have?

4. Each Excel workbook may only have one data model

5. Name the four valid text file formats that you can find in Get & Transform.

5. Text, CSV, JSON, and XML.

6. If you want to be able to choose related tables when connecting to a SQL Server Database, what two things need to be in place for the related tables functionality work?

6. First, the database must have referential integrity in place, and second you need to include relationship columns in the SQL Server Database connection configuration window.

7. When you create a connection to an external data source such as an Analysis Services Database, where is connection information stored? A. Office XML (.oml File) B. Office Data Connection File (.odc) C. ODATA File D. ODBC

7. Answer B: Office Data Connection File.

8. Which Data Source in Get & Transform Data allows you to connect to live data? A. Analysis Service Tabular B. Analysis Services Multi-Dimensional C. Text Files D. Web Page

8. Answers A and B: Analysis Service Tabular and Dimensional.

9. When ingesting files using the From Folder or From SharePoint Folder, what characters must the files share?

9. The files all must share the same structure.

When loading data into your model you use the Query Editor and you select Load To. You are asked to select how you would like to view this data in your worksheet. Which selection will not load the data to a traditional Excel object? A. Create Connection Only B. Table C. PivotTable Report D. PivotChart

Answer A: Create Connection Only creates a connection to the source only. Data is not loaded to a Table, PivotTable Report, or PivotChart, but you do have the option of loading the data to the Excel Data Model.