Dynamic Calculated Column Creation Macro - provide parameters

[RhysWilliams]

Hi All

I'm hoping some one might have an idea how to help, I'll work through my example below.

I need to add a large number of repeated calculation columns onto my data set based on existing columns and I want to make this is dynamic and re-usable.

The existing columns are split into three groups where the column header is prefixed with the grouping. I want to be able to pass to a macro the column calculation required and new kpi name in a list. Its mostly divisions so I'll go with that for this example, but I could take it a step further and provide calculation type.

List data into macro would be as follows then also providing the data.

New Calc Column Name | numerator Column | denominator Column

e.g.

total_atv | "total_sales_net" | "total_volume"

group1_atv | "group1_sales_net" | "group1_volume"

group2_atv | "group2_sales_net" | "group2_volume"

The column headers are the same for each group apart from the text preceding the _ so I think I could go a step further and just pass the below to the macro, and the new column is prefixed with the group with the macro repeating 3 times for each grouping. Run 1 Prefix "total", run 2 prefix "group 1" ect..

atv | "sales_net" | "volume"

Any pointers would be appreciated, 

[RhysWilliams]

Hi, 

I have renamed the columns now and did not take a version of the flow before with the error.

If it crops up again I'll post, but thank you again for your solution.

[RhysWilliams]

Hi Dan

I think the issue is around the update value's part of the macro replacing the worlds sales or volume when they appear at the end of my column headers. Would you expect this behavior? My attempts to change to using numerator and denominator have failed.

Do you have any advice where I'm going wrong?

The macro works when running sample data. I have checked my live data and all the columns supplied in the kpi table are present with values but i receive the below

[danilang]

Hi @RhysWilliams 

Can you post a small sample of the live data?  Just enough to demonstrate the issue that you're having?

Dan

[RhysWilliams]

Hi Dan

See attached Package, let me know if the package does not work as I've not exported like this before.

I've included your original macro and then my one with the changes. Both macros fail, your's processes all apart from the last kpi calc where i think a replace issue is occuring and i think mine is failing due to how I have amended your macro.

Thanks for the Help

[RhysWilliams]

Need to select the specific expression line to change in the macro rather than the first level of forumla.

All sorted now