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 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!
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
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
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.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |