Forum Discussion
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 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!
13 Replies
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.
Thank you. I will give this a try and will keep you apprised! Thanks, again!
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!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.
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.Best Regards,
Muhammad YousafIf this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.
