Excel data import in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Excel Data Import Options in Power BI

When importing Excel data into Power BI, which option correctly describes the difference between 'Import' and 'DirectQuery' modes?

A Both 'Import' and 'DirectQuery' load data into Power BI's memory but differ in refresh speed.

B 'Import' queries the Excel file live, while 'DirectQuery' loads data into Power BI's memory.

C 'Import' loads data into Power BI's memory, while 'DirectQuery' queries the Excel file live without loading data.

D 'Import' only works with CSV files, while 'DirectQuery' works with Excel files.

💡 Hint

Think about whether data is stored inside Power BI or accessed live.

'Import' mode loads the Excel data into Power BI's internal storage for fast analysis. 'DirectQuery' mode keeps the data in the Excel file and queries it live, so no data is stored inside Power BI.

❓ dax_lod_result

intermediate

DAX Measure for Counting Rows from Imported Excel Data

You imported an Excel table named 'SalesData' into Power BI. Which DAX measure correctly counts the total number of rows in 'SalesData'?

A TotalRows = COUNTROWS(SalesData)

B TotalRows = COUNT(SalesData)

C TotalRows = SUM(SalesData)

D TotalRows = DISTINCTCOUNT(SalesData)

💡 Hint

Think about which DAX function counts rows, not values.

COUNTROWS counts the number of rows in a table. COUNT and SUM expect a column, and DISTINCTCOUNT counts unique values in a column.

❓ visualization

advanced

Best Visualization for Showing Excel Data Import Status

You want to create a Power BI dashboard visual that clearly shows the status of Excel data imports over time (e.g., success, failure, duration). Which visual type is best suited for this?

A Line chart showing cumulative number of imports over time

B Stacked column chart showing counts of success and failure per day

C Table visual listing each import with status and duration columns

D Pie chart showing percentage of success vs failure overall

💡 Hint

Consider which visual best compares categories over time.

A stacked column chart allows you to compare success and failure counts side by side for each day, making trends easy to spot. Pie charts don't show time trends, tables are less visual, and line charts show cumulative counts but not status breakdown.

❓ data_modeling

advanced

Handling Multiple Excel Sheets in Power BI Data Model

You imported two Excel sheets: 'Customers' and 'Orders'. To analyze orders by customer, what is the best way to model these tables in Power BI?

A Create a relationship between 'Customers[CustomerID]' and 'Orders[CustomerID]' columns

B Use 'Customers' table only and ignore 'Orders' table

C Append 'Customers' and 'Orders' tables into one combined table

D Create a calculated column in 'Orders' that duplicates 'Customers' data

💡 Hint

Think about how tables relate in real life: customers place orders.

Creating a relationship between the CustomerID columns allows Power BI to connect orders to customers for analysis. Appending tables mixes different data types, ignoring orders loses data, and duplicating data wastes space and complicates updates.

🔧 Formula Fix

expert

Troubleshooting Excel Data Import Refresh Failure

After importing an Excel file into Power BI, the data refresh fails with an error: 'OLE DB or ODBC error: External table is not in the expected format.' What is the most likely cause?

A The Excel file is open in another program during refresh

B The Excel file is saved in CSV format instead of XLSX

C Power BI does not support Excel files larger than 1 MB

D The Excel file has unsupported features like macros or tables with merged cells

💡 Hint

Think about what Excel features might cause format issues during import.

Unsupported features like macros, merged cells, or complex tables can cause Power BI to fail reading the Excel file format. The file being open usually causes a different error. Power BI supports large files, and CSV is a different format, not XLSX.