Forum Discussion
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:
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....
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
3 Replies
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
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.
