Question

Automating Monthly Sales Data Consolidation from SharePoint
You receive monthly sales data in multiple Excel files stored in a SharePoint folder. The file structure remains consistent, but new files are added over time. How can you automate data consolidation in Power BI using Power Query to ensure seamless updates when new files are added?

Answer 1

Power Query has been used to automate monthly sales data consolidation from a SharePoint folder into Power BI. The following describes the processes for doing this:

1. Connecting to SharePoint Folder

In Power BI, Get Data → SharePoint Folder.

Enter the SharePoint URL (not a direct file link).

Click Transform Data to initiate the Power Query Editor.

2. Filtering and Combining Only Required Files

In the Content column, filter out everything except files with an appropriate naming convention (e.g., "Sales_*.xlsx").

Binary. Combine will combine all Excel files.

Expand relevant tables/sheets.

3. Making Data Consistent

Make sure that all column names and formats are the same.

Use the Remove Duplicates option when needed.

Create a custom column to report the Source. Name (file name) for tracking.

4. Loading and Automating Refresh

Close & Apply to load the data into Power BI.

Schedule the Refresh in Power BI Services, ensuring the correct gateway authentication is set for SharePoint.