Loading to worksheet or data model in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to load data into an Excel worksheet.

Excel

QueryTable.Refresh BackgroundQuery=[1]

Drag options to blanks, or click blank then click option'

ATrue

BFalse

CNone

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using True causes the query to run asynchronously, delaying data load.

Using 0 or None are invalid for this property.

Setting BackgroundQuery to False ensures the query runs synchronously and loads data directly into the worksheet.

2fill in blank

medium

Complete the code to load data into the Excel data model using Power Query.

Excel

WorkbookConnection.ModelTable.LoadToDataModel = [1]

Drag options to blanks, or click blank then click option'

A0

BFalse

CTrue

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or 1 instead of True/False causes errors.

Setting False loads data only to worksheet, not data model.

Setting LoadToDataModel to True loads the query results into the Excel data model.

3fill in blank

hard

Fix the error in the code to correctly load data into the worksheet.

Excel

ActiveWorkbook.Queries.Add Name:="SalesData", Formula:= "let Source = Excel.CurrentWorkbook(){[Name=\"Table1\"]}[Content] in Source"
ActiveWorkbook.Connections.Add2 Name:="SalesData", _
    Description:="", _
    ConnectionString:= "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$", _
    CommandText:= Array("SELECT * FROM [SalesData]"), _
    lCmdtype:= [1]

Drag options to blanks, or click blank then click option'

AxlCmdText

BxlCmdTable

CxlCmdDefault

DxlCmdSql

Attempts:

3 left

💡 Hint

Common Mistakes

Using xlCmdSql is invalid in Excel VBA.

Using xlCmdTable expects a table name, not SQL text.

The CommandType should be xlCmdText when using SQL command text in the connection.

4fill in blank

hard

Fill both blanks to load data into worksheet and refresh it.

Excel

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Range([1]), _
    Destination:=Range("A1"))
    .QueryTable.Refresh BackgroundQuery:= [2]
End With

Drag options to blanks, or click blank then click option'

A"Table1"

BTrue

CFalse

D"Sheet1!Table1"

Attempts:

3 left

💡 Hint

Common Mistakes

Using just "Table1" may cause range not found error.

Setting BackgroundQuery True delays data loading.

The Source must be a valid range reference like "Sheet1!Table1" and BackgroundQuery should be False to refresh synchronously.

5fill in blank

hard

Fill all three blanks to load Power Query data into the data model and refresh it.

Excel

Dim cn As WorkbookConnection
Set cn = ActiveWorkbook.Connections.Add2(Name:= "PQConnection", _
    Description:= "Power Query Connection", _
    ConnectionString:= "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$", _
    CommandText:= Array("let Source = Excel.CurrentWorkbook(){[Name=\"Table1\"]}[Content] in Source"), _
    lCmdtype:= [1])
cn.ModelTable.LoadToDataModel = [2]
cn.Refresh [3]

Drag options to blanks, or click blank then click option'

AxlCmdText

BTrue

CFalse

DxlCmdDefault

Attempts:

3 left

💡 Hint

Common Mistakes

Using xlCmdDefault causes command text to be ignored.

Setting LoadToDataModel False loads only to worksheet.

Using Refresh True delays data availability.

Use xlCmdText for SQL command, set LoadToDataModel True to load into data model, and Refresh with BackgroundQuery False for synchronous refresh.

Practice

1. What is the main difference between loading data to a worksheet and loading data to the data model in Excel?

easy

A. Loading to worksheet displays data as a visible table; loading to data model keeps data hidden but usable for analysis.

B. Loading to worksheet compresses data; loading to data model duplicates data.

C. Loading to worksheet only works with text data; loading to data model only works with numbers.

D. Loading to worksheet automatically creates charts; loading to data model creates pivot tables.

Solution

1. Step 1: Understand worksheet loading

   Loading data to a worksheet shows the data as a visible table you can directly see and use.

2. Step 2: Understand data model loading

   Loading data to the data model keeps the data hidden but ready for advanced analysis like relationships and pivot tables.

3. Final Answer:

   Loading to worksheet displays data as a visible table; loading to data model keeps data hidden but usable for analysis. -> Option A

4. Quick Check:

   Visible table vs hidden model = C [OK]

Hint: Worksheet shows data; data model hides it for analysis [OK]

Common Mistakes:

• Thinking data model shows data as a table
• Confusing data compression with loading method
• Assuming worksheet loading only works with text

2. Which of the following is the correct way to load data into Excel's data model using Power Query?

easy

A. Click 'Close & Load To...' and select 'New Worksheet'.

B. Click 'Close & Load To...' and select 'Table'.

C. Click 'Close & Load To...' and select 'Only Create Connection'.

D. Click 'Close & Load To...' and select 'Pivot Table'.

Solution

1. Step 1: Identify loading to data model option

   Choosing 'Only Create Connection' loads data to the data model without showing it in a worksheet.

2. Step 2: Differentiate from worksheet loading

   Selecting 'Table' or 'New Worksheet' loads data visibly; 'Pivot Table' creates a report but not data model loading.

3. Final Answer:

   Click 'Close & Load To...' and select 'Only Create Connection'. -> Option C

4. Quick Check:

   'Only Create Connection' = data model load [OK]

Hint: Use 'Only Create Connection' to load data model [OK]

Common Mistakes:

• Choosing 'Table' which loads data to worksheet
• Selecting 'Pivot Table' which creates report, not data model
• Confusing 'New Worksheet' with data model loading

3. You load a large sales dataset into Excel using Power Query. You choose 'Only Create Connection' and add it to the data model. What is the expected behavior when you create a PivotTable from this data?

medium

A. PivotTable will fail because data is not loaded into any worksheet.

B. PivotTable will automatically load data into a new worksheet.

C. PivotTable will show an empty table because data is hidden.

D. PivotTable will be created using the data model without loading data into a worksheet.

Solution

1. Step 1: Understand data model loading effect

   Loading data as 'Only Create Connection' adds data to the data model but does not show it in worksheets.

2. Step 2: PivotTable behavior with data model

   PivotTables can use data from the data model directly, so it will create a report without needing worksheet data.

3. Final Answer:

   PivotTable will be created using the data model without loading data into a worksheet. -> Option D

4. Quick Check:

   PivotTable uses data model directly = B [OK]

Hint: PivotTables can use data model without worksheet data [OK]

Common Mistakes:

• Assuming PivotTable needs visible worksheet data
• Thinking PivotTable will fail without worksheet table
• Believing PivotTable auto loads data to worksheet

4. You tried to load data to the data model but accidentally selected 'Table' instead of 'Only Create Connection'. What problem might occur?

medium

A. Data will load to worksheet, increasing file size and slowing performance.

B. Data will not load anywhere and cause an error.

C. Data will load only to the data model but not visible in worksheet.

D. Excel will automatically fix the loading method without user action.

Solution

1. Step 1: Identify effect of loading as 'Table'

   Loading as 'Table' loads data visibly into a worksheet, which can increase file size and slow Excel if data is large.

2. Step 2: Contrast with data model loading

   'Only Create Connection' loads data hidden in data model, avoiding worksheet clutter and performance issues.

3. Final Answer:

   Data will load to worksheet, increasing file size and slowing performance. -> Option A

4. Quick Check:

   Loading as Table = visible data, bigger file [OK]

Hint: Loading as Table shows data, may slow large files [OK]

Common Mistakes:

• Thinking Excel auto fixes loading method
• Believing data won't load if wrong option chosen
• Confusing data model loading with worksheet loading

5. You have multiple large tables to analyze with relationships in Excel. Which loading method should you choose to optimize performance and enable advanced analysis?

hard

A. Load all tables as visible tables in separate worksheets.

B. Load all tables as connections only and add them to the data model.

C. Load half the tables to worksheets and half to data model randomly.

D. Load tables as CSV files outside Excel for analysis.

Solution

1. Step 1: Consider performance with large tables

   Loading large tables as visible worksheets increases file size and slows Excel.

2. Step 2: Use data model for relationships and analysis

   Loading tables as connections only and adding to data model keeps data hidden, improves performance, and allows creating relationships for advanced analysis.

3. Step 3: Avoid random or external loading

   Random loading causes confusion; external CSVs are outside Excel and not integrated.

4. Final Answer:

   Load all tables as connections only and add them to the data model. -> Option B

5. Quick Check:

   Data model + connections = best for large related tables [OK]

Hint: Use data model for large related tables to boost speed [OK]

Common Mistakes:

• Loading all data visibly causing slow Excel
• Mixing loading methods without plan
• Using external files instead of Excel data model