Help with Transforming Data

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.