Adding rows in existing table based on a list that can be obtained from current table

[MichaelQi]

Hi,

I am trying to use power query to automatically insert rows to existing table based on a list of dates that I can obtain from the existing table. The table has project name, date and amount. I am trying to sum up certain projects for each date.

Old Table:

Project      Date               Amount

proj1         1/1/2019        10

proj2         1/1/2019        10

proj3         1/1/2019        10

proj1         1/2/2019        20

proj2         1/2/2019        20

proj3         1/2/2019        20

New Table:

Project      Date               Amount

proj1         1/1/2019        10

proj2         1/1/2019        10

proj3         1/1/2019        10

proj1         1/2/2019        20

proj2         1/2/2019        20

proj3         1/2/2019        20

projSum    1/1/2019        30

projSum    1/2/2019        60

Can this be done?

Thank you.

[Anonymous · Accepted solution]

You can do this in Power Query, though I'm not 100% why you want to materialize this figure when a measure would be better suited, but maybe you have a reason.

You can see the applied steps, but quickly:

1. Duplicate original table

2. Group this table by Date, and Aggregating by Sum of Amount

3. Add a column named "Project" and enter in "ProjectSum"

4. Append your main table with table

5. Turn off "enable load" on the Append table

file:

https://1drv.ms/u/s!Amqd8ArUSwDS3S0XTbCuNhNXsZvp?e=EEYRIf

[MichaelQi]

I got it working, thank you so much! The reason why I am doing this is because I need to mannually calculate some summary value for my report. I am trying to find some automatic method.