cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelQi Frequent Visitor
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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

 

Final Table.png

 

file:

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

View solution in original post

2 REPLIES 2
Super User
Super User

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

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

 

Final Table.png

 

file:

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

View solution in original post

MichaelQi Frequent Visitor
Frequent Visitor

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

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)