Splitting row into 2 rows based on field value

[branners]

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 additional row and the revenue split between the two people.

Data:

Salesperson 1 | Salesperson 2 | Revenue

Joe Bloggs | null | $3500

Sarah Jane | John Blog | $5000

if Salesperson 2 contains a value, create as new row with Revenue split across both salespeople.

Expected Output:

Salesperson | Revenue

Joe Bloggs | $3500

Sarah Jane | $2500

John Blog | $2500

Any revenue with a second salesperson must be split between the 2 SPs.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Revenue"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"GroupTables", each _, type table [Revenue=number, Index=number, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Divisor", each Table.RowCount([GroupTables])),
    #"Expanded GroupTables" = Table.ExpandTableColumn(#"Added Custom", "GroupTables", {"Revenue", "Attribute", "Value"}, {"Revenue", "Attribute", "Value"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GroupTables", "Custom", each [Revenue]/[Divisor]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Divisor", "Attribute", "Revenue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Revenue"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Revenue", type number}})
in
    #"Changed Type"

Hope this helps.

[v-yalanwu-msft]

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.  

[AntrikshSharma]

@branners  Try this:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WCk7MSS0uSC0qzs9TMFSoUUDmGwH5QallqXmlqUqxOtFKXvmpCk45+enpxUCJvNKcHCClYmxqYACWDU4sSsxQ8ErMSwUKe+Vn5IHVgpQAVQCVxAIA",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Column1 = _t ]
    ),
    PromotedHeaders = Table.PromoteHeaders ( Source, [ PromoteAllScalars = true ] ),
    RenamedColumns = Table.RenameColumns (
        PromotedHeaders,
        { { "Salesperson 1 | Salesperson 2 | Revenue", "Data" } }
    ),
    SplitTextIntoList = Table.AddColumn (
        RenamedColumns,
        "Split To List",
        each Splitter.SplitTextByDelimiter ( "|" )( [Data] )
    ),
    ExtractNumbers = Table.AddColumn (
        SplitTextIntoList,
        "Get Number",
        each
            if Text.Contains ( [Data], "Revenue" ) = false then
                Number.From ( Text.Trim ( List.Reverse ( [Split To List] ){0} ) )
            else
                null,
        Int64.Type
    ),
    RemoveNulls = Table.AddColumn (
        ExtractNumbers,
        "Remove Nulls",
        each List.RemoveItems (
            List.Transform (
                List.FirstN ( [Split To List], List.Count ( [Split To List] ) - 1 ),
                each Text.Trim ( _ )
            ),
            { "null" }
        )
    ),
    ConvertToTable = Table.AddColumn (
        RemoveNulls,
        "Names",
        each Table.FromList ( [Remove Nulls] )
    ),
    Result = Table.AddColumn (
        ConvertToTable,
        "Salesperson Amount",
        ( x ) => Table.AddColumn ( x[Names], "Amount", each x[Get Number] )
    ),
    RemovedOtherColumns = Table.SelectColumns ( Result, { "Salesperson Amount" } ),
    ExpandedSalespersonAmount = Table.ExpandTableColumn (
        RemovedOtherColumns,
        "Salesperson Amount",
        { "Column1", "Amount" },
        { "Column1", "Amount" }
    ),
    RenamedColumns1 = Table.RenameColumns (
        ExpandedSalespersonAmount,
        { { "Column1", "Sales Person" } }
    ),
    ChangedType1 = Table.TransformColumnTypes (
        RenamedColumns1,
        { { "Sales Person", type text }, { "Amount", Int64.Type } }
    )
in
    ChangedType1

[branners]

Wow thank you so much, I will give this a try but looks bang on.

Thank you for the quick reply.

[vanessafvg · Accepted solution]

I tried something in power query for you, check it out and see if it works for you.