Forum Discussion

Jeff2Jets's avatar
Jeff2Jets
Helper III
1 year ago

Accruing values weekly

I want to create a Power BI table that contains the following columns for every employee:   Employee name, Department, # of Calls   A Report is generated for me every week within one of our syste...
Need Help
Show and Tell
Tips and Tricks
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi Jeff2Jets ,

To make this automatic, create a new folder where you will place the .csv files.

And in excel instead of importing the single file every time, you can import a folder directly. please follow these steps.

1- Go to Power query > New Source > More

 

2- Now go to All > Folder and click connect

 

3- Now browse the folder and click ok:

 

4- Now click Combine & Transform Data:


5- Select the week1 and click ok:

 

6-  Delete the Source.Name column

 

7- Now lets split the column by delimiter (considering that the values are in one column separated by comma):

8- Now split by comma for each occurence and click ok:

 

Now my table look like this:

 

Check that my columns headers are in the first row and in the 4 row, lets correct this:

 

9- Now use the first row as header:

 

10- Now drop down the filter in Employee name column and uncheck the Employee name, this will ensure to hide all row containing this name:



Now my table look like this:

 

Now follow the steps from 3 till the end to group # of calls from my previous repply.

Note: The table names (name of sheets) of your .csv should be exactly the same for all files.

Now for new weeks, you just need to place the file in created folder and go to power bi and refresh to ensure that the new data is loaded.

 

 

 

 

 

Jeff2Jets's avatar
Jeff2Jets
Helper III
1 year ago

I am so sorry, but I am not having success. I very much appreciate all of your help, but would it be possible for you to create a full list of ordered, numbered instructions, so when I don't see what you are showing, I can reference the step number? Again, I appreciate your help and patience.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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.