cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Dynamic column names needed in a dynamic sum equation

Hi guys,

 

I need to pivot a column full of payslip IDs with values that is their pay amount for the fortnight. The payslip IDs are incremental so I am able to extract only the latest 6 payslips and then perform the pivot. But I need to sum the payslips which now are columns with dynamically changing names the next time it is run. How do i make it so that the payslip numbers can update to the latest 5 payslips? 

 

Power Query > Add Custom field:

Sum of =

[Payslip 1] + [Payslip 2] + [Payslip 3] + [Payslip 4] + [Payslip 5] + [Payslip 6] = XYZ

 

For example these columns below 453, 452, 451, 450, 449 and 448:

 

dynamic columns.jpg

 

I need to create a new Custom Column to add the values in Columns 453, 452, 451, 450, 449 and 448 but how do I make the column names dynamic instead of 453, 452, 451, 450, 449, 448 as these payslip IDs will change incrementally as I extract only the latest 6 payslips....

dynamic columns in sum equation.jpg

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

Hi @jeju 

 

Not sure if you really need to unpivot those columns, keep them then sum up, but yes, there are ways to do in M. I created dummy data to show 2 ways in the file. You need to amend the logic accordingly for finding the latest 6 columns, and remove values not in these columns - last6 list and the list.sum

 

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

I'd strongly recommend against trying to do it this way. Life will be much easier keeping things unpivoted and doing any summing with DAX measures rather than Power Query custom columns.

Syndicate_Admin
Administrator
Administrator

Hi @jeju 

 

Not sure if you really need to unpivot those columns, keep them then sum up, but yes, there are ways to do in M. I created dummy data to show 2 ways in the file. You need to amend the logic accordingly for finding the latest 6 columns, and remove values not in these columns - last6 list and the list.sum

 

View solution in original post

Thank you, that was exactly what I wanted. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (2,193)