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   ...
  • Vijay_A_Verma's avatar
    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"