Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
99 | |
97 | |
73 | |
72 |