Forum Discussion

branners's avatar
branners
New Member
5 years ago
Solved

Splitting row into 2 rows based on field value

Hi   Been going around in circles here, really hope someone can help.   We have potentially 2 sales people on a given deal. If there is a second salesperson, they should be created as an addition...
Need Help
v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
5 years ago

Hi, branners 

 

According to your description, If your data is in one column, you could split them in power query first，

then create a revenue column , and use union()   to create a new table.  the following formula to create :

Step1：split column in power query

Step2: create revenue column

revenue =
IF (
    [Salesperson 2] = BLANK ()
        || [Salesperson 1] = BLANK (),
    [Revenue3] / 1,
    [Revenue3] / 2)

Step3:  Create a new table

newtable =
VAR _a =
    SUMMARIZE (
        FILTER ( 'Table1', [Salesperson 1] <> BLANK () ),
        [Salesperson 1],
        [revenue]
    )
VAR _b =
    SUMMARIZE (
        FILTER ( 'Table1', [Salesperson 2] <> BLANK () ),
        [Salesperson 2],
        [revenue]
    )
RETURN
    UNION ( _a, _b )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.