excelpowerquery

Split column depending on content


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.


Solution

  • 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"
    

    enter image description here

    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"