Power Query & Power Pivot in Excel

Welcome to your free Power Query & Power Pivot in Excel test

1.

A Calendar table can be created from:

PowerBI.com

Power Query

Power Pivot

Either Power Query or Power Pivot

2.

To reduce the risk of Excel crashing when using Power Query and Power Pivot:

Close all other applications

Upgrade to 64 bit Office

Add extra memory

All of the above

3.

To retain the formatting you apply in an Excel Pivot Table:

Use Conditional Formatting to automatically re-format cells

On the Analyze Pivot Table Ribbon, check the “Preserve cell formatting on update” checkbox

Under Pivot Table Options, check the “Preserve cell formatting on update” checkbox

Check the “Retain cell formatting” button within Power Query

4.

Selecting the “Only Create Connection” option when creating a data connection to a table will:

Only load the table’s data into the data model

Create a connection to the table, but do nothing else

Only load the table’s data into the spreadsheet

Create an encrypted connection to the target data source

5.

To calculate a year-to-date Sales value from your data, you could use the following DAX expression:

=TOTALYTD( [Sales], tblCalendar[Date] )

=SUM( [Sales] * 365)

=SUM( [Sales], YTD)

=SUMYTD( [Sales], tblCalendar[Date] )

6.

The data in the Excel data model is stored:

On hidden Excel sheets

In the Power BI data model, within Excel

On Microsoft Azure servers

All of the above

7.

The values in an Excel Pivot Table are calculated by:

Applying a series of filters that depend on the row and column the value sits in

Applying a series of iterative calculations to derive each value

Applying CUBE formulas to calculate each value

Applying GETPIVOTDATA formulas to calculate each value

8.

To transfer parameters from Excel into Power Query:

Use a Range Name in Excel

Use a Named Query

Use a custom function

Use the Power Query “Connect Parameter” function

9.

To create an Excel CUBEVALUE formula:

Click inside a Pivot Table then go to “OLAP Tools” > “Convert to Formulas”

Type the CUBE formula directly into any blank Excel cell

Use Excel’s Function Wizard

Any of the above

10.

To comment out a line of code in the Power Query Advanced Editor:

Use #

Use \\

Use /*

Use //

Be sure to click the button below to check your answers! By checking your answers, you agree to receive communications from Access Analytic. You can unsubscribe at any time. Click here to review our Privacy Policy.

First Name

Email

Related Posts:

Share This Story, Choose Your Platform!