Question 18

Explanation

Solution with Loading and Refreshing CSV Files

You have CSV files (e.g., "SalesWeek1.csv," "SalesWeek2.csv") in a SharePoint Online folder, updated weekly, and need to combine them into a single Power BI dataset with automatic updates. The SharePoint Folder connector and scheduled refresh provide the solution. Here’s how:

• Load and Combine with SharePoint Folder Connector:

  • In Power BI Desktop, click "Get Data" > "SharePoint Folder." Enter the SharePoint site URL (e.g., "https://northwind.sharepoint.com/sites/Sales"). Sign in with Microsoft 365 credentials.

  • Filter to the folder (e.g., "Shared Documents/WeeklySales"), showing CSVs. Click "Combine" > "Combine & Transform Data."

  • Power Query assumes identical structures (e.g., Date, Product, Amount), selects a sample file (e.g., "SalesWeek1.csv"), and combines all—e.g., 500 rows from 5 files (100 each). Remove extra columns (e.g., "Source.Name") if needed, then "Close & Load."

• Configure Scheduled Refresh:

  • Publish to Power BI Service (Home > "Publish," select workspace, e.g., "Sales Workspace").

  • In Power BI Service, go to the dataset’s "Settings" > "Scheduled Refresh." Toggle "Keep your data up to date," set "Refresh frequency" to "Weekly," pick a day/time (e.g., Monday 8:00 AM post-update), and save.

  • Result: New files (e.g., "SalesWeek6.csv") or updates load weekly—e.g., Monday refresh adds 100 new rows, reflecting the latest data.

Why This Works

• Efficient Combination: SharePoint Folder connector merges CSVs in one step, handling identical structures dynamically—e.g., 10 files combine in ~1 minute, per SharePoint Folder docs.

• Automatic Updates: Scheduled refresh pulls the latest CSVs from SharePoint Online without manual intervention, matching weekly updates, per scheduled refresh. No gateway needed—SharePoint Online is cloud-based.

• Domain Fit: "Prepare the data" covers loading and consolidating files in Power Query, with refresh setup ensuring current data.

The Microsoft Power BI refresh guide states, “Combine files with SharePoint Folder and use scheduled refresh for automated updates from cloud sources.” This ensures your report stays current efficiently.

Why Not Other Approaches?

• Text/CSV per file with manual append is slow—e.g., 10 files take 10+ minutes vs. one-step combine; no auto-update.

• SharePoint List connector targets lists, not CSV contents; gateway is irrelevant for cloud SharePoint.

• Downloading to local and using Folder connector works but requires manual file sync—misses weekly automation.

This method combines and updates your dataset seamlessly.