Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.