Is it possible to use dynamic formulas to operate multiple columns?

Hi Community,

I would like your support with this question.

Is it possible, I don't know if with the formula tool, separate these columns into positive and negative values all the columns at once?

I have tried and I know that with formula I can do one at a time, but is it possible to do it dynamically with a single expression or using a tool?

MiniData.xlsx

Datasets

Tips and Tricks

Dynamic Processing

Preparation

Optimization

Expression

Accepted answers

TonyA

Here's one solution. This uses a crosstab tool and there are a couple of things you need to keep in mind. The crosstab tool generally sorts columns it processes left to right, lowest to highest. Also, it replaces any special characters or spaces with underscores. In this solution, I renamed the columns to get the plus and minus signs, but did not do anything about the sort order. If that is important you can add a prefix to each column name before crosstabbing and then remove it afterwards with Dynamic Rename (similar to what I did to replace the plus and minus signs).

Please mark this as a solution if it resolves your question.

SortPosNeg.yxmd

All comments

benakesh

Hi @Ivanmt01 ,

Need more details about expected output or you can share expected output .

You can transpose , filter and cross tab the data together . The filter separates +ve and -ve numbers. Sample wf attached for reference.

formula.yxmd

TomWelgemoed

Hey @Ivanmt01 ,

Yes, and I think the attached is a nice and clean solution.

You pivot the values first so you can get all the values in 1 column. Once that is done, all you need to do is give each value a column name (either positive or negative) and then you can cross-tab it back into the appropriate column.