topic Search and assemble cell values into new column in DAX Commands and Tips

Search and assemble cell values into new column

Anonymous — Tue, 27 Sep 2022 09:22:23 GMT

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!

Re: Search and assemble cell values into new column

Anonymous — Wed, 28 Sep 2022 02:29:35 GMT

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

Re: Search and assemble cell values into new column

Anonymous — Wed, 28 Sep 2022 09:30:57 GMT

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