Appending report refresh data historical data

Hi bzeeblitz 

 

If the files have the same structure (same sets of columns, same worksheets/tables to connect to), use the SharePoint Folder connector to connect to them. Navigate to the folder by filtering the path and use Power BI's combine feature once you're in the folder.

You'll need to connect to the SharePoint site first, as the connector doesn't provide an option to directly open a SharePoint folder. The SharePoint site URL typically looks like this: https://organization.sharepoint.com/sites/sitename/. Replace "organization" and "sitename" with the actual organization and site name.

 

Sign-in using your Microsoft account under your org.  Click transform data after signing in

Filter the Folder path the the folder that contains the files

You can the folder path by selecting a file in SharePoint then clicking details. The path is after more details.

 

https://z4cqt.sharepoint.com/sites/testsite/Shared%20Documents/Real%20Estate%20Sample/2000-2010/2010.xlsx
The %20 in the URL represents a space character. When URLs are created, spaces are not allowed, so they are encoded as %20. Each %20 has to be replaced with a space. Other characters in the url are encoded differently as well. You just need the path up to before the filename. The actual text string to be used in the filter is "https://z4cqt.sharepoint.com/sites/testsite/SharedDocuments/Real Estate Sample/2000-2010/". Please note that the last forward slash is included.

 

Click the combine and transform icon to combine and transform the files.

In most cases, the transformations using the GUI are just enough.

 

Note: Connecting to a SharePoint site with a large number of files can be very slow.

 

 

Hi bzeeblitz ,

You should use Power BI's SharePoint Folder connector to extract all Excel files.

• Open Power BI Desktop.
• Click on Home > Get Data > More.
• Select SharePoint folder as the data source.
• 

   

• Enter your SharePoint site URL (not the Document Library URL, just the site).
• 

   

• Click OK and sign in with your credentials.

 

• After loading, Power BI will show a list of all files in the SharePoint folder.
• Apply a filter to select only the Excel files you need (e.g., files starting with "Masterdata").
• Click on Combine > Combine & Transform Data.
• In Power Query Editor, select the correct sheet or table in your Excel files.
• Ensure that the column names are consistent across all files.
• Remove unnecessary columns like Source.Name if not needed.
• Click Close & Apply.