Skip to content

AMAZING Power BI & Excel CALL 08 6210 8500

Pay my invoice
Contact Us

Amazing Power BI
Amazing Excel
Solver
Microsoft Fabric
Insightful Training
Resources
Blog
About Us

Power Query & Power Pivot in Excel

Home/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

None

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

None

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

None

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

None

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] )

None

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

None

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

None

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

None

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

None

10.

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

Use #

Use \\

Use /*

Use //

None

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

Time's up

Related Posts:

Dynamic Data Validation with Tables in Excel
Streamlining Power Query Code Documentation with GPT-4: A…
Leveraging ChatGPT for Transformative Finance Functions: A…
Build Wordle using Dynamic Array Functions
Wordle for Power BI Fans
New Year Power Query Challenge

Jackie Meddows-Taylor2024-10-04T10:59:07+08:00

Share This Story, Choose Your Platform!

Facebook Twitter LinkedIn Email

Subscribe

* indicates required

Email *

First Name *

Last name *

Business name

Country

State

Newsletter Sign Up

First name(Required)

Email address(Required)

CAPTCHA

Δ

Categories

Arrays
Auditing
Budgeting/Forecasting
Business Intelligence
CFOs
Charts
ChatGPT
Data Handling
Excel
Excel 2007
Excel 2013
Excel 2016
Financial Modelling
Formatting
Functions
Keyboard
Microsoft Fabric
Mouse
Office 365
Pivot Tables
Power BI
Power Pivot
Power Query
Printing
Productivity
Teams
Uncategorized
VBA – Macros

Learn from highly qualified finance professionals with international experience

Reach out to the Access Analytic team Monday to Friday on (08) 6210 8500 or submit your contact information and we will be in touch.

Get Ahead of the Game – Book Your Training Now!

First Name(Required)

Last Name(Required)

Business Name

Email Address(Required)

Training Options(Required)

Message

Consent

I agree to receive communications from Access Analytic. You can unsubscribe at any time. Click here to review our Privacy Policy.

CAPTCHA

Δ

Microsoft Power BI
Microsoft Excel
Solver Software
Training & Courses

Free Resources
Blog Articles
About Us
Pay My Invoice

Contact Us

Suite 143, Level 1, 580 Hay Street Perth, WA

Phone: (08) 6210 8500

Do you want to optimise your business efficiencies?

Download our 2 FREE Solver white papers on Budgeting Process and Monthly Reporting today.

First Name(Required)

Email(Required)

Δ

Copyright 2024 Access Analytic | All Rights Reserved | Privacy Policy | Engagement Terms & Conditions

Brought to you by: Huddle Up Creative

LinkedIn Twitter Facebook Rss YouTube Email

Jeff is the founding Director and Principal Business Analyst at Access Analytic Solutions. He has over 20 years’ experience in chartered accounting, business management and consulting.

Jeff holds a Masters in Applied Finance and is a Certified Information Systems Auditor. He is highly regarded and recognised around the world as an expert in the field of financial modelling and Power BI reporting. His achievements have been recognised by his peers and he is a Fellow of both the Financial Services Institute of Australasia and the Australian Institute of Management. Jeff has extensive experience in best practice management reporting, data analytics, business analysis, and financial modelling and is also an entertaining international presenter and trainer on these subjects.

Jeff is the author of a number of white papers on financial modelling on subjects such as Financial Modelling Best Practices and Financial Model Auditing. He provides training for companies throughout Australia, New Zealand, Asia, South Pacific, the Middle East, and Africa.

Yury Tokarev is a highly accomplished finance professional and international trainer with over 25 years of experience in financial modelling and advanced analytics. As a Director at Access Analytic, Yury specialises in leveraging data-driven insights to enhance strategic decision-making through sophisticated analytical tools, including Power BI and AI.

Yury’s extensive background includes developing complex financial models across various industries, automating Excel-based solutions for efficient data management, and designing Power BI reports that integrate a variety of metrics, including financial, people, and ESG indicators. His expertise uniquely positions him to address the evolving needs of senior financial professionals as they embrace digital finance and sustainability initiatives.

Prior to joining Access Analytic, Yury gained significant experience in investment banking, corporate finance, and business services and advisory. These roles provided him with a deep understanding of strategic financial planning, complex financial transactions, and advisory services, which he now leverages to deliver cutting-edge solutions in digital finance transformation and analytics.

As an engaging and interactive trainer, Yury has delivered numerous workshops and training sessions globally, helping professionals elevate their understanding of financial modelling, Power BI, and AI analytics. His teaching approach combines practical insights with real-world applications, empowering participants to effectively apply advanced analytics and AI in their strategic roles.

Wyn Hopkins
B.Sc. (Hons) Accounting & Financial Management, Microsoft MVP, Excel Expert (MS), Author

Wyn has been awarded MVP status by Microsoft every year since 2017 and is the author of the book “Power BI for the Excel Analyst”. He is known worldwide for sharing his knowledge via the Access Analytic YouTube channel with over 5 million views and 90,000+ subscribers.

He gained his Chartered Accountant qualification at PwC in 2000 and has extensive Business Analyst experience with HBOS and Barclays in the UK plus multinational clients in Perth.

Wyn is skilled in Excel and Power BI development, Power Query automation, management reporting and is an experienced Excel and Power Bl trainer.

Ready to kickstart your career?

To express your interest in our Internship Program, please fill out the form and attach your cover letter and resume, and we’ll get back to you shortly!

First Name(Required)

Last Name(Required)

Email address(Required)

Phone number

Why do you want to intern with us?(Required)

Resume(Required)

Drop files here or

Max. file size: 300 MB.

Cover letter

Drop files here or

Max. file size: 300 MB.

Consent(Required)

I confirm that I am a student currently enrolled at a university in Perth and residing in Perth.(Required)

Δ

First Name(Required)

Email(Required)

So we can contact you with updates - you can opt out any time.

Δ

First Name(Required)

Email(Required)

So we can contact you with updates - you can opt out any time.

Δ

First Name(Required)

Last Name(Required)

Email Address(Required)

Topic

Phone Number

Business Name

Preferred method of contact

Email

Phone

Message

CAPTCHA

Δ

First Name(Required)

Last Name(Required)

Email Address(Required)

Phone Number

Business Name

What is your enquiry in regards to?

Message

Consent

I agree to receive communications from Access Analytic. You can unsubscribe at any time. Click here to review our Privacy Policy.

CAPTCHA

Δ