Accruing values weekly

[Jeff2Jets]

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 systems, from which I can export a .csv file. 

When the .csv is first ingested into Power BI, it has the following rows:

John Smith, Sales, 12

Judy Brown, Support, 7

The second week produces the following rows within the .csv file:

John Smith, Sales, 3

Judy Brown, Support, 11

Stephanie Williams, Support, 4

I want to be able to:

1. Refresh the list of employees with any new ones, without deleting/adding the table.

2. Increment the # of Calls for each employee.

So after the 2nd week, the table would have:

John Smith, Sales, 15

Judy Brown, Support, 18

Stephanie Williams, 4

So Stephanie was added to the table and the # of Calls for John and Judy were incremented.

Can this be done in Power BI and if so, how?

Thanks in advance!

[muhammad_786_1]

Hy @Jeff2Jets,

Thanks, @Bibiano_Geraldo for your quick response!

I just wanted to share the file where I implemented the solution. Please feel free to check the attached files, and I hope it will be helpful for you.

File

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[Bibiano_Geraldo]

Hi @Jeff2Jets ,

When you import your weekly .csv files into Power BI, consolidate them into a single table. You can do this by using Append Queries in Power Query following bellow steps:

Import the .csv files for Week 1 and Week 2 into Power BI.
Go to Power Query and Select Home > Append Queries and append all weekly tables into a single table.
Note: Use the combined table as your main dataset. This ensures all new data is automatically added on refresh.

Now create a summarized table to calculate the total calls for each employee:

EmployeeCalls = 
SUMMARIZE(
    'CombinedTable',
    'CombinedTable'[Employee Name],
    'CombinedTable'[Department],
    "Total Calls", SUM('CombinedTable'[# of Calls])
)

When you add new rows in the .csv files, they will automatically be included during the refresh because you're appending all files into a single table in Power Query. Power BI will then aggregate the calls correctly.

[Jeff2Jets]

Sorry for my confusion, but I don't fully understand your solution. I understand the first 3 steps below, but not sure how to specifically append the two weekly tables. Would you be able to provide very specific instructions for the remainder of the steps?
1. Create a blank Report.

2. Import Week1.csv.
3. Import Week2.csv.
4. I have no idea what 'append all weekly tables' means.

Again, sorry for my confusion!

[Bibiano_Geraldo]

Hi @Jeff2Jets ,

 lets do it together.

1-after loaded the weeks, append queries as new in power query:

2- append the table like bellow and hit ok:

Now you data should look like this:

3- Now select the employee name and department and right click on them and choose group by:

4- now make sure that you fill the fields like this and hit ok:

Now your data should look like this:

As you can see is now showing your desired result

5- now disable the load for week1 and week 2, because we will not use them:

After disabled week1 and week2 tables, now you can rename the appended table to your desired name and close and apply.

[Jeff2Jets]

I am so sorry for this, but I was just asked if I could add the '# of Answers' column to the Reports and provide a running total for these, like you are helping me do for '# of Calls'. Is it possible to group on two columns? If so, how does that change the instructions you sent? Again, sorry about the new requirement.

[Bibiano_Geraldo]

hi @Jeff2Jets ,

To make sure i understood you well, please, can you show the desired output should look like?

[Jeff2Jets]

Employee name, Department, # of Calls, # of Answers

Week1.csv

John Smith, Sales, 12, 9

Judy Brown, Support, 7, 5

Week2.csv

John Smith, Sales, 3, 2

Judy Brown, Support, 11, 8

Stephanie Williams, Support, 4, 3

Combined

John Smith, Sales, 15, 11

Judy Brown, Support, 18, 13

Stephanie Williams, 4, 3

[Bibiano_Geraldo]

Hi @Jeff2Jets,

In step 4 on my reply, just add the aggregation for # of answer, and make sure that the aggregation is sum.

[Jeff2Jets]

I followed your instructions and now have a new table named Statistics that has totals from the two tables, Thank you!

When I have a new file (Week3.csv), what must I do to have this file add to the Statistics table? 

[Bibiano_Geraldo]

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]

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]

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.

[Jeff2Jets]

Thank you. I will give this a try and will keep you apprised! Thanks, again!