Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
I tried something in power query for you, check it out and see if it works for you.
Proud to be a 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.
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.
@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
Wow thank you so much, I will give this a try but looks bang on.
Thank you for the quick reply.
I tried something in power query for you, check it out and see if it works for you.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |