Help with Transforming Data

[bposa217]

I am trying to transition our commission reporting from Excel into Power BI, but I'm having trouble splitting out (and then summing) the margin (commission) based upon the way the data is brought into the model.

The data comes in likes this:

MatchID	CreditClientOwner	CreditCandidateOwner	PPE	Margin	Client Side	Candidate Side
142242	John Smith	George Washington	9/29/2019	$192.00	$96.00	$96.00
112560	Abraham Lincoln	Abraham Lincoln	9/29/2019	$767.13	$383.57	$383.57
139749	George Washington	Abraham Lincoln	9/29/2019	$1,063.55	$531.78	$531.78
151035	Abraham Lincoln	John Smith	9/29/2019	$1,042.94	$521.47	$521.47

But I ultimately need to get it into this type of format/matrix:

Recruiter	Client Side	Candidate Side	Total
Abraham Lincoln	$     905.04	$              915.34	$  1,820.38
John Smith	$        96.00	$              521.47	$     617.47
George Washington	$     531.78	$                 96.00	$     627.78

The biggest problem I have is that there are two names (owners) per row (transaction), so leveraging caculated columns thus far hasn't provided me the breakthrough I'm looking for.  I'm assuming I'm going to need to create a master "Salesperson" table that lists all of the sales team members we have, but I stumble upon the relationship aspect because not all names will necessarily appear in both the client and candidate owner columns.

Any help is appreciated!

[VasTg · Accepted solution]

@bposa217 

My recommendation for Enterprise architecture would be different but taking a simpler approach for Excel.

All right. Here you go.

I copied your sample data and created an Entity/Table, changed datatypes, Use first row as Headers..Highlighed in Red Text.

I selected CreditClientOwner and CreditCandidateOwner and hit Unpivot Only selected Columns under Transform ribbon. Highlighted in Blue

Created 2 new conditional columns (New Client Side and New Candidate Side) to show corresponding values. Highlighted in green

The final table looks like below.

Here is the expected results with new total measure (SUM('Table'[New Candidate Side])+SUM('Table'[New Client Side]))

Query Editor Steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBNi8IwEIb/SikeSzaTj8YcRUWUlRU87KH0EG1oAppCDPj3TbLItouLEIZn3kwewjRNuVfhbLarsiqXXnc2LC9Wu/B1d9r/Zsp1tlNBP+PDYR3rXvneujSV3xRH2+nUPad/grZqSmCEMBLvdoNxxfFqg4nNRg++18W3uhnr+jAklfwg8WCQkWcgCcI4kawnkJ1AeJ2yxckro67Fp3Xn4eJeJhOtqAUCmojOKeJiRFlMpWDyn/+9UUOF6yjiiTkFJOYjynIOmPKXoslu/loZQZJlFwHExIja9gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"MatchID", Int64.Type}, {"CreditClientOwner", type text}, {"CreditCandidateOwner", type text}, {"PPE", type date}, {"Margin", Currency.Type}, {"Client Side", Currency.Type}, {"Candidate Side", Currency.Type}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"CreditClientOwner", "CreditCandidateOwner"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Client Or Candidate"}, {"Value", "Recruiter"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "New Client Side", each if [Client Or Candidate] = "CreditClientOwner" then [Client Side] else 0),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "New Candidate Side", each if [Client Or Candidate] = "CreditCandidateOwner" then [Candidate Side] else 0),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"New Client Side", type number}, {"New Candidate Side", type number}})
in
#"Changed Type2"

If this helps, Accept it as a solutions.

Kudos are good too.

[bposa217]

This worked perfectly!  Thanks for your help.

[Thedatadude]

Interesting... really excited to see the community answer.