cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
branners
New Member

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 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.

 

1 ACCEPTED SOLUTION
vanessafvg
Super User
Super User

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yalanwu-msft
Community Support
Community Support

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

v-yalanwu-msft_0-1620265132507.jpeg

Step2: create revenue column

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

v-yalanwu-msft_1-1620265132508.png

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:

v-yalanwu-msft_2-1620265132510.png

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
Resident Rockstar
Resident Rockstar

@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

pbi community.png

branners
New Member

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

 

Thank you for the quick reply.

vanessafvg
Super User
Super User

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors