SharePoint and OneDrive sources in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding SharePoint Folder Connector in Power BI

Which of the following best describes how Power BI connects to a SharePoint folder to load files?

A Power BI uses the SharePoint Folder connector to list all files in the folder and then allows filtering and combining files.

B Power BI connects directly to each file in SharePoint without listing the folder contents first.

C Power BI requires downloading files manually from SharePoint before importing them.

D Power BI uses the Web connector to access SharePoint files by URL only.

💡 Hint

Think about how Power BI handles multiple files in a SharePoint folder.

The SharePoint Folder connector lists all files in the folder, enabling users to filter and combine files easily. This is the recommended way to connect to SharePoint folders.

❓ dax_lod_result

intermediate

Calculating Total Sales from OneDrive Excel Source

You imported sales data from an Excel file stored in OneDrive. The table 'SalesData' has columns: Date, Product, and SalesAmount.

What is the result of this DAX measure?

 TotalSales = SUM(SalesData[SalesAmount]) 

Assuming the SalesAmount column has values: 100, 200, 150, 50.

A 500

B 150

C 100

D Syntax Error

💡 Hint

SUM adds all values in the column.

The SUM function adds all values in the SalesAmount column: 100 + 200 + 150 + 50 = 500.

❓ visualization

advanced

Best Visualization for File Size Distribution from SharePoint Folder

You loaded a SharePoint folder containing many files with different sizes. You want to show the distribution of file sizes grouped by file type.

Which visualization is best suited for this purpose in Power BI?

A Line Chart showing file size over time

B Pie Chart showing count of files per file type

C Stacked Column Chart showing total file size per file type

D Table showing file names and sizes

💡 Hint

Think about comparing total sizes by categories.

A Stacked Column Chart effectively shows total file sizes grouped by file type, making it easy to compare size distribution visually.

🔧 Formula Fix

advanced

Troubleshooting SharePoint Folder Access Error

You try to connect Power BI to a SharePoint folder but get an error: 'Access Denied'. Which of the following is the most likely cause?

A The SharePoint folder is empty and cannot be connected.

B You do not have permission to access the SharePoint site or folder.

C Power BI does not support SharePoint folder connections.

D The Excel files in the folder are corrupted.

💡 Hint

Think about permissions when accessing online folders.

'Access Denied' usually means the user lacks permission to access the SharePoint site or folder. Permissions must be granted to connect successfully.

🎯 Scenario

expert

Combining Multiple Excel Files from OneDrive with Different Structures

You have multiple Excel files stored in OneDrive, each with sales data but different column orders and some missing columns. You want to combine them into one table in Power BI.

Which approach will handle this scenario best?

A Use the SharePoint Folder connector and rely on automatic column matching without transformations.

B Manually download and combine files in Excel before importing to Power BI.

C Import only one file and ignore others to avoid mismatched columns.

D Use Power Query to import all files, promote headers, and use 'Append Queries' with 'Use First Row as Headers' and 'Column Matching' enabled.

💡 Hint

Think about how Power Query can transform and combine data with different structures.

Power Query allows importing multiple files, promoting headers, and appending queries with column matching, handling different column orders and missing columns effectively.