I have column similar to this:
Column A |
---|
spa:34,spd:76 |
spa:21 |
,spd:2 |
spa:99,spd:12 |
I would like to split the column and get this:
Column A | Column B |
---|---|
spa:34 | spd:76 |
spa:21 | |
spd:2 | |
spa:99 | spd:12 |
How can I achieve this in Power Query?
I know how to split a column with a delimiter but if the first value is missing in a cell the value gets placed in the wrong column.
This should work and is a bit more generalized than what you describe. If you need exactly what you describe, that requires a small modification.
M Code (from advanced editor)
let
//Change next line to reflect actual table name or other data source
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//set data type to type text
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}),
//Determine first vs second values
#"First/Second" =
let
//find cell with two entries
both = List.Generate(
()=>[a=Text.Split(#"Changed Type"{0}[Column A],","),idx=0],
each List.Count([a]) =2,
each [a = Text.Split(#"Changed Type"{[idx]+1}[Column A],","), idx = [idx]+1],
each List.RemoveItems([a],{""}))
in
List.Transform(List.Last(both), each Text.Split(_,":"){0}),
#"Add Splitted Column" = Table.AddColumn(#"Changed Type", "Splitted", (r)=>
if Text.Contains(r[Column A],",")
then Record.FromList(Text.Split(r[Column A],","),{"Column A", "Column B"})
else Record.FromList(
List.ReplaceRange(
{null,null},
List.PositionOf(#"First/Second",Text.Split(r[Column A],":"){0}),
1,
{r[Column A]}),
{"Column A","Column B"}),
type [Column A=nullable text, Column B=nullable text]
),
#"Removed Columns" = Table.RemoveColumns(#"Add Splitted Column",{"Column A"}),
#"Expanded Splitted" = Table.ExpandRecordColumn(#"Removed Columns", "Splitted", {"Column A","Column B"})
in
#"Expanded Splitted"
If you want EXACTLY
what you write handling only spa:
and spd:
, you can use the following code instead:
let
//Change next line to reflect actual table name or other data source
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//set data type to type text
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}),
#"Add Splitted Column" = Table.AddColumn(#"Changed Type","Splitted", (r)=>
let
split = Text.Split(r[Column A],","),
spa = List.FindText(split,"spa:"){0}?,
spd = List.FindText(split, "spd:"){0}?
in [Column A = spa, Column B = spd],
type [Column A=nullable text, Column B=nullable text]),
#"Removed Columns" = Table.RemoveColumns(#"Add Splitted Column",{"Column A"}),
#"Expanded Splitted" = Table.ExpandRecordColumn(#"Removed Columns", "Splitted", {"Column A", "Column B"})
in
#"Expanded Splitted"