Dynamic column names needed in a dynamic sum equation

[Anonymous]

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:

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....

[Vera_33 · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

Thank you, that was exactly what I wanted. 

[AlexisOlson]

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.