Forum Discussion

Hyuna_8000's avatar
Hyuna_8000
Helper I
4 years ago
Solved

How to split a single column into double columns, without a delimiter

Hi,

 

I have a table that has team name and team member in the same column:
Input:

Team1
Lily Zane
Danielle Garner
Martin Timberlake
 
Team2
Rachel Marie
Suzie Qu
 
Team3
Jason Chan
Sarah Alba

 

I was wondering if the column can be split into 2 columns, one is the team name, the other one is team member:

Output:

TeamMember Name
Team1 Lily Zane
Team1 Danielle Garner
Team1 Martin Timberlake
Team2Rachel Marie
Team2Suzie Qu
Team3Jason Chan
Team3Sarah Alba

 

This is a tricky one because it cannot be seperated by delimiter etcs, any ideas would be much appreciated.

 

Thanks

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYy9CgJBDIRfJWxtoz6BKAiihXqVyxZzEthgLkLOK/TpPbOVzTA/H5Nz6hjDMpVFTkfRN91gHGkHE1Zl2sONPboT/CVGnQw9u+LRyJDfzSrcBffKSjMrbb9OH2E6T//wOtwB49NoW2ENhaPSRnukUr4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Input] <> "" and [Input] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Team", each if Text.StartsWith([Input], "Team") then [Input] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Team"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Input], "Team")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Input", "Member Name"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Team", "Member Name"})
in
    #"Reordered Columns"

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYy9CgJBDIRfJWxtoz6BKAiihXqVyxZzEthgLkLOK/TpPbOVzTA/H5Nz6hjDMpVFTkfRN91gHGkHE1Zl2sONPboT/CVGnQw9u+LRyJDfzSrcBffKSjMrbb9OH2E6T//wOtwB49NoW2ENhaPSRnukUr4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Input] <> "" and [Input] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Team", each if Text.StartsWith([Input], "Team") then [Input] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Team"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Input], "Team")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Input", "Member Name"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Team", "Member Name"})
in
    #"Reordered Columns"