Log In
Register
Home
Active Member
December 5, 2023
Offline
Hi,
I am new to power query and have been asked to complete a power query workbook that splits a premium figure based on a percentage each month.
My current custom column looks like the below and will specify the multiplication of a percent and a financial value across 72 different columns.
[MonthPremium1 = try ([Premium] * [MonthPerc1]) otherwise "",
MonthPremium2 = try ([Premium] * [MonthPerc2]) otherwise "",
MonthPremium3 = try ([Premium] * [MonthPerc3]) otherwise ""
........]
My desired solution would be to loop through them with a variable counting to 72. Something like the below. Would this or something similar be possible?
[X = 1
While X <=72
"MonthPremium" & X & "= try ([Premium] * [MonthPer" & X & "]) otherwise """"
X = X + 1
END
]
Moderators
January 31, 2022
Offline
It's difficult to follow what the code does without seeing the underlying data. Could you share a workbook with some example data (non confidential but realistic) and the desired result.
Active Member
December 5, 2023
Offline
Hi Riny,
Apologies, please see the example workbook attached.
In the workbook you should see the desired output as created by the long-winded custom column formula.
Thanks for your time on this.
Moderators
January 31, 2022
Offline
Thanks! I see what you are doing and understand that you don't want this custom column referencing each and every column. The solution would be to unpivot the data, creating a much narrower but longer table where you can add a column that multiplies two columns. Extract the month number that are used in the headers of Table1 and I believe you have the basis for a report. For instance, a pivot table with the premiums per month by company.
I have just done that in the attached file. Would that work for you?
Active Member
December 5, 2023
Offline
Hi Riny,
Thanks very much for your help here. It does provide the desired outcome.
I believe I understand the concept and it does mitigate the need to add the column several times. I have carried on the solution to join back to the original table so the pivot table is not needed.
Thank you again for your help.
1 Guest(s)
