Accruing values weekly

Hi Jeff2Jets ,

Automating Data Import from a Folder in Power BI

This guide will show you how to automatically load and transform data from multiple CSV files stored in a folder using Power Query in Power BI.

Step 1: Set Up the Folder for Your Files

1. Create a folder on your computer where you'll save the .csv files.
2. Ensure that the structure and column headers of all CSV files are the same.

Step 2: Connect Power Query to the Folder

1. Open Power Query in Power BI by navigating to Home > Transform Data > Data Source Settings.
2. Click New Source > More.
3. From the Get Data window, go to All > Folder and click Connect.
4. Browse and select the folder containing your CSV files, then click OK.

Step 3: Combine Files in the Folder

1. In the next dialog, click Combine & Transform Data.
2. When prompted to select a sample file, choose one (e.g., "Week1.csv") and click OK.
• This file will serve as a template for transformations applied to all files.

Step 4: Prepare and Clean the Data

1. In the Power Query editor, review the preview of your data.
2. Delete the Source.Name column if it appears (this column contains the file name).
3. If your data is in a single column with values separated by commas:
• Select the column, then go to Transform > Split Column > By Delimiter.
• Choose Comma ( , ) as the delimiter and select Each Occurrence, then click OK.

Step 5: Adjust Column Headers and Rows

1. If column headers are in the first row of your table:
• Go to Transform > Use First Row as Headers to promote the first row to headers.
2. If unnecessary rows (e.g., "Employee Name") appear:
• Filter them out by clicking the dropdown in the relevant column (e.g., "Employee Name").
• Uncheck the value(s) you want to remove and click OK.

Step 6: Group and Summarize Data

1. Select the columns Employee Name and Department (or any other relevant columns).
2. Right-click and choose Group By.
3. In the Group By window:
• Group By: Select the column(s) to group.
• Operation: Choose an aggregation (e.g., "Sum").
• Click OK.

Step 7: Load Data

1. Click Close & Apply to load the transformed data into Power BI.

Step 8: Automate Data Refresh for New Files

• To add data for new weeks:
1. Save the new CSV file(s) in the same folder.
2. Open Power BI and click Refresh to load the new data automatically.

Important Notes

• Ensure that all CSV files have the same structure and column headers for consistent results.
• Regularly verify the folder path to ensure new files are saved in the correct location.