Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ABR002
Helper I
Helper I

Split column and rejoin?

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#RegionRevenueTeam Members
123North$100,000John Doe, Sally Smith, Dan Brown
456South$200,000John Doe, Dan Brown
789South$300,000Alex Shepard
321East$400,000Alex Shepard, John Doe
654East$500,000Jane Jones, Alex Shepard
987North$600,000Dan 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 1Team Member 2 Team Member 3Revenue
321John DoeAlex Shepard $400,000
654Alex ShepardJane Doe $500,000
987Jane DoeDan 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 MemberRevenueNumber of Accounts
Jane Doe$1,100,0002
Alex Shepard$1,200,0003

 

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.

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

Hi @ABR002 ,

in Power Query you can do it like this:

 

03-06-_2021_00-03-35.png

 

// 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)

View solution in original post

1 REPLY 1
FrankAT
Community Champion
Community Champion

Hi @ABR002 ,

in Power Query you can do it like this:

 

03-06-_2021_00-03-35.png

 

// 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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.