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

[Hyuna_8000]

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:

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

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

Thanks

[Vijay_A_Verma · Accepted solution]

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"

[Hyuna_8000]

Thanks @Vijay_A_Verma , it works!