Forum Discussion
Hyuna_8000
4 years agoHelper I
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 ...
- 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"
Vijay_A_Verma
4 years agoMost Valuable Professional
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_80004 years agoHelper I
Thanks Vijay_A_Verma , it works!