Forum Discussion
Help with Transforming Data
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!
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.
3 Replies
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.
This worked perfectly! Thanks for your help.
Interesting... really excited to see the community answer.
