SharePoint and OneDrive sources in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business analyst at a company that stores project documents and reports on SharePoint and OneDrive.

📋 Request: Your manager wants you to create a Power BI report that shows the total number of documents uploaded each month from SharePoint and OneDrive sources.

📊 Data: You have access to two data sources: a SharePoint document library list and a OneDrive folder file list. Each source provides file names, upload dates, and file sizes.

🎯 Deliverable: Create a Power BI report with a line chart showing monthly document uploads from SharePoint and OneDrive separately, and a card visual showing the total documents uploaded from both sources combined.

Sample Data

Source	File Name	Upload Date	File Size (MB)
SharePoint	ProjectPlan.docx	2024-01-15	1.2
SharePoint	Budget.xlsx	2024-01-20	0.8
OneDrive	MeetingNotes.docx	2024-01-22	0.5
OneDrive	DesignMockup.png	2024-02-05	2.3
SharePoint	StatusReport.pdf	2024-02-10	1.0
OneDrive	Presentation.pptx	2024-02-15	3.1
SharePoint	Requirements.docx	2024-03-01	1.5
OneDrive	Summary.xlsx	2024-03-03	0.9
SharePoint	Timeline.xlsx	2024-03-10	0.7
OneDrive	Notes.docx	2024-03-15	0.4

1

Step 1: Import the SharePoint and OneDrive data into Power BI as a single combined table with columns: Source, File Name, Upload Date, File Size.

Use Power BI's 'Get Data' feature to connect to SharePoint document library and OneDrive folder, then append queries to combine data.

Expected Result

A combined table with 10 rows showing all files from both sources.

2

Step 2: Create a new calculated column 'Upload Month' to extract the month and year from 'Upload Date' for grouping.

Upload Month = FORMAT('Table'[Upload Date], "yyyy-MM")

Expected Result

A new column showing values like '2024-01', '2024-02', '2024-03' for each row.

3

Step 3: Create a measure to count the number of documents uploaded.

Document Count = COUNT('Table'[File Name])

Expected Result

A measure that returns the total count of files in the current filter context.

4

Step 4: Create a line chart visual with 'Upload Month' on the X-axis, 'Document Count' as the Y-axis, and 'Source' as the legend to separate SharePoint and OneDrive uploads.

Configure visual fields: X-axis = Upload Month, Y-axis = Document Count, Legend = Source

Expected Result

A line chart showing monthly document uploads for SharePoint and OneDrive separately.

5

Step 5: Create a card visual to show the total number of documents uploaded from both sources combined.

Use the 'Document Count' measure without any filters.

Expected Result

A card showing the total document count: 10.

Final Result

Monthly Document Uploads

Upload Month | SharePoint | OneDrive
-------------------------------------
2024-01     |     2      |    1
2024-02     |     1      |    2
2024-03     |     2      |    2

Total Documents Uploaded: 10

✓SharePoint and OneDrive both contribute to document uploads each month.

✓January had 3 uploads total, February had 3, and March had 4.

✓OneDrive uploads increased in February compared to January.

Bonus Challenge

Add a measure to calculate the average file size uploaded per month for each source and display it in a table visual.

Show Hint

Create a measure using AVERAGE('Table'[File Size (MB)]) and use 'Upload Month' and 'Source' as rows and columns in the table.

Practice

1. Which option in Power BI allows you to connect directly to files stored in SharePoint Online?

easy

A. SQL Server connector

B. SharePoint Folder connector

C. Azure Blob Storage connector

D. Local File connector

Solution

1. Step 1: Identify the correct connector for SharePoint Online

   Power BI provides a specific connector called 'SharePoint Folder' to access files stored in SharePoint Online.

2. Step 2: Eliminate other connectors

   SQL Server and Azure Blob Storage connectors are for different data sources, and Local File connector is for files on your computer.

3. Final Answer:

   SharePoint Folder connector -> Option B

4. Quick Check:

   SharePoint Folder = SharePoint Folder connector [OK]

Hint: Use 'SharePoint Folder' to connect to SharePoint files quickly [OK]

Common Mistakes:

• Choosing SQL Server connector for SharePoint data
• Using Local File connector for cloud files
• Confusing Azure Blob Storage with SharePoint

2. Which of the following is the correct URL format to connect Power BI to a SharePoint document library using the Web connector?

easy

A. https://company.sharepoint.com/sites/sitename/_layouts/15/start.aspx

B. https://company.sharepoint.com/sites/sitename/documents

C. https://company.sharepoint.com/sites/sitename/_api/web/lists/getbytitle('libraryname')/items

D. https://company.sharepoint.com/sites/sitename/libraryname

Solution

1. Step 1: Understand the URL needed for Web connector

   The Web connector requires the direct URL to the SharePoint document library root folder, which looks like https://company.sharepoint.com/sites/sitename/libraryname.

2. Step 2: Analyze other URL options

   https://company.sharepoint.com/sites/sitename/_layouts/15/start.aspx is a SharePoint start page, not a document library URL. https://company.sharepoint.com/sites/sitename/_api/web/lists/getbytitle('libraryname')/items is an API endpoint, not a direct folder URL. https://company.sharepoint.com/sites/sitename/documents is incorrect because 'documents' is not the standard library name unless customized.

3. Final Answer:

   https://company.sharepoint.com/sites/sitename/libraryname -> Option D

4. Quick Check:

   Correct SharePoint library URL = https://company.sharepoint.com/sites/sitename/libraryname [OK]

Hint: Use the direct library URL without extra paths for Web connector [OK]

Common Mistakes:

• Using SharePoint homepage URL instead of library URL
• Using API URLs instead of folder URLs
• Adding extra SharePoint layout paths

3. Given this Power Query M code snippet connecting to SharePoint folder:

Source = SharePoint.Files("https://company.sharepoint.com/sites/sales", [ApiVersion = 15])
Filtered = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx"))

What does the 'Filtered' step return?

medium

A. All files in the SharePoint site ending with .xlsx

B. Only folders in the SharePoint site

C. All files except those ending with .xlsx

D. Only files from local drive

Solution

1. Step 1: Understand SharePoint.Files function

   This function lists all files and folders in the specified SharePoint site URL.

2. Step 2: Analyze the filter condition

   The filter keeps only rows where the file name ends with '.xlsx', so it selects Excel files only.

3. Final Answer:

   All files in the SharePoint site ending with .xlsx -> Option A

4. Quick Check:

   Filter by '.xlsx' files = All files in the SharePoint site ending with .xlsx [OK]

Hint: Text.EndsWith filters file extensions easily [OK]

Common Mistakes:

• Thinking it filters folders instead of files
• Assuming it excludes .xlsx files
• Confusing SharePoint files with local files

4. 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?

medium

A. You do not have permission to access the SharePoint site

B. You used the wrong SharePoint URL format

C. Power BI does not support SharePoint connections

D. Your local file path is incorrect

Solution

1. Step 1: Understand the error message

   An "Access denied" error usually means the user lacks permission to access the SharePoint site or folder.

2. Step 2: Check other options

   Wrong URL format usually causes "Not found" errors, not access denied. Power BI supports SharePoint connections. Local file path is irrelevant for SharePoint connection.

3. Final Answer:

   You do not have permission to access the SharePoint site -> Option A

4. Quick Check:

   Access denied = Permission issue = You do not have permission to access the SharePoint site [OK]

Hint: Access denied usually means permission problem [OK]

Common Mistakes:

• Assuming URL format causes access denied
• Thinking Power BI can't connect to SharePoint
• Confusing local file errors with SharePoint errors

5. You want to create a Power BI report that automatically updates when new Excel files are added to a OneDrive folder. Which approach is best?

hard

A. Connect using SharePoint Folder connector with a static file path

B. Download files manually and import them into Power BI Desktop

C. Connect using OneDrive Folder connector and enable scheduled refresh

D. Use SQL Server connector to link OneDrive files

Solution

1. Step 1: Identify the correct connector for OneDrive files

   Power BI has a OneDrive Folder connector that connects directly to OneDrive folders and supports automatic refresh.

2. Step 2: Understand refresh options

   Enabling scheduled refresh allows the report to update when new files are added. Manual download is inefficient. SQL Server connector is unrelated. SharePoint Folder connector is for SharePoint, not OneDrive.

3. Final Answer:

   Connect using OneDrive Folder connector and enable scheduled refresh -> Option C

4. Quick Check:

   OneDrive Folder + scheduled refresh = Connect using OneDrive Folder connector and enable scheduled refresh [OK]

Hint: Use OneDrive Folder connector with refresh for auto updates [OK]

Common Mistakes:

• Manually importing files instead of connecting live
• Using SharePoint connector for OneDrive data
• Trying to use SQL Server connector for cloud files