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.