Search and assemble cell values into new column

[Anonymous]

Hi all,

I need help to search and cut out values from a cell and assemble them in a new row.

I have a semicolon as separator and I need always the first string until an slash (in case an semicolon exists then next needed value would be after the semicolon until the next slash).

In case the values (if there are multiple - separated by semicolon) are same, then write only once in the new column (2nd row in example).

Visualized should it look like this:

Is	ToBe
Enterprise Support/ Information Management;Procurement & Logistics/ Supplier CM/ Supplier Identification; Production/ Maintanence	Enterprise Support, Procurement & Logistics, Production
Enterprise Support/ Information Management;Enterprise Support	Enterprise Support
Finance & Controlling	Finance & Controlling

Thanks in advance!

[Anonymous]

Thanks a lot for your investigation! But it does not work on my side...

1. The search for "/" seems not to work

2. The case without / is not handled.

Have you any Input for me?

Thanks in advance, Marko

[Anonymous]

HI @Anonymous,

You can add a custom column with a custom function to achieve your requirement:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "ToBe", each 
            Function.Invoke((x as list)=> 
                let
                    Replaced= 
                        if List.Count(x) > 1 
                        then List.RemoveNulls(List.Transform(x, each 
                            if Text.Contains(_,"/") 
                            then Text.Start(_, Text.PositionOf(_,"/")) 
                            else null )) 
                        else x,
                    Result =Text.Combine(Replaced,",")
                in 
                    Result
            ,{List.Distinct(Text.Split([Is],";"))} ))

Result:

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc4xC4NADAXgvxJuLigdujhKC0KFgqO94TijBDSRmPv/vR4UCp26vcDLxxtHd2VD3ZUOhCHtu6hV0PEsugUjYegDhwU3ZGseKjFpyfBMdX2+wF0WOoziUZXvlVCh7b+ObsptmikWrYFsTCm+c5VpYguMHNH5019TfqtFuBGHrH3WtcKmsq7Ei/P+BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Is = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Is", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ToBe", each 
            Function.Invoke((x as list)=> 
                let
                    Replaced= 
                        if List.Count(x) > 1 
                        then List.RemoveNulls(List.Transform(x, each 
                            if Text.Contains(_,"/") 
                            then Text.Start(_, Text.PositionOf(_,"/")) 
                            else null )) 
                        else x,
                    Result =Text.Combine(Replaced,",")
                in 
                    Result
            ,{List.Distinct(Text.Split([Is],";"))} ))
in
    #"Added Custom"

Regards,

Xiaoxin Sheng