Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I honestly have no idea how to phrase the issue I'm facing, but essentially I need to split the values in a column, and then have them be back together in a visual/table.
I am dealing with a, let's just say less than perfect data source. I need to list the revenue and number of accounts each member on a team was a participant in. The problem is that all the team members are listed in the same column, like in the table below for example
Account# | Region | Revenue | Team Members |
123 | North | $100,000 | John Doe, Sally Smith, Dan Brown |
456 | South | $200,000 | John Doe, Dan Brown |
789 | South | $300,000 | Alex Shepard |
321 | East | $400,000 | Alex Shepard, John Doe |
654 | East | $500,000 | Jane Jones, Alex Shepard |
987 | North | $600,000 | Dan Brown, Jane Jones |
What I need to do is show what each participant account for. As an example, Alex Shepard was on accounts 789, 321, and 654, so he was on 3 accounts which produced $1,200,000. Jane Jones was on 654 and 987. so 2 accounts and $1,100,000.
I did do a split column by delimiter, and that essentially gives me this:
Account# | Team Member 1 | Team Member 2 | Team Member 3 | Revenue |
321 | John Doe | Alex Shepard | $400,000 | |
654 | Alex Shepard | Jane Doe | $500,000 | |
987 | Jane Doe | Dan Brown | $600,000 |
But I need to figure out how to give Jane Doe credit for 654 and 987, but not 321, and Alex Shepard Credit for 321 and 654 but not 987.
This is the desired outcome:
Team Member | Revenue | Number of Accounts |
Jane Doe | $1,100,000 | 2 |
Alex Shepard | $1,200,000 | 3 |
I did come up with a way to do what I need with the data in hand, it involves using a Dax measure to search if the cell contains a string of text. The problem is that new people are added from time to time, so when someone new joins the team, it won't know what to search for, and since I am building this for someone I am not always going to be there to add the new team members. It's also hard as the team member combinations are always different, so I can't just group a team together.
I know this is a rather weird request but it's just the way the data was presented. Again, I need this to be something that can automatically update itself.
Solved! Go to Solution.
Hi @ABR002 ,
in Power Query you can do it like this:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY89C8IwEIb/ypE5g/nqx6jUxcElY8gQMBChJqWtqP/eM5BaqbfccTwP750xhHFBKDmncQ7Y2e5TOJxSiNAlT0G7vn+Bvl3nQKFzEQ5jekRiqSFSVYjqdM8u37q/eN20K1wUfN/7J+jgBzdeMic4w/XRTTM2+Q+jUDKyUCn5FdRyhoseuegnCpuMtqlXb1fFWQ7GhEUn1r4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account#" = _t, Region = _t, Revenue = _t, #"Team Members" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account#", Int64.Type}, {"Region", type text}, {"Revenue", Int64.Type}, {"Team Members", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Team Members", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Team Members.1", "Team Members.2", "Team Members.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Team Members.1", type text}, {"Team Members.2", type text}, {"Team Members.3", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Team Members.1", Text.Trim, type text}, {"Team Members.2", Text.Trim, type text}, {"Team Members.3", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Team Members.1", Text.Clean, type text}, {"Team Members.2", Text.Clean, type text}, {"Team Members.3", Text.Clean, type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Cleaned Text", {"Account#", "Region", "Revenue"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Team Member"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Team Member"}, {{"Revenue", each List.Sum([Revenue]), type nullable number}, {"Number of Accounts", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Take a look at the attached PBIX file >> Power Query Eitor (Transform Data).
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi @ABR002 ,
in Power Query you can do it like this:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY89C8IwEIb/ypE5g/nqx6jUxcElY8gQMBChJqWtqP/eM5BaqbfccTwP750xhHFBKDmncQ7Y2e5TOJxSiNAlT0G7vn+Bvl3nQKFzEQ5jekRiqSFSVYjqdM8u37q/eN20K1wUfN/7J+jgBzdeMic4w/XRTTM2+Q+jUDKyUCn5FdRyhoseuegnCpuMtqlXb1fFWQ7GhEUn1r4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account#" = _t, Region = _t, Revenue = _t, #"Team Members" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account#", Int64.Type}, {"Region", type text}, {"Revenue", Int64.Type}, {"Team Members", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Team Members", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Team Members.1", "Team Members.2", "Team Members.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Team Members.1", type text}, {"Team Members.2", type text}, {"Team Members.3", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Team Members.1", Text.Trim, type text}, {"Team Members.2", Text.Trim, type text}, {"Team Members.3", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Team Members.1", Text.Clean, type text}, {"Team Members.2", Text.Clean, type text}, {"Team Members.3", Text.Clean, type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Cleaned Text", {"Account#", "Region", "Revenue"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Team Member"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Team Member"}, {{"Revenue", each List.Sum([Revenue]), type nullable number}, {"Number of Accounts", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Take a look at the attached PBIX file >> Power Query Eitor (Transform Data).
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |