Forum Discussion
Conditional Column on dynamical Combination delimiters values
Try this custom column
=let mylist=Text.Split([Fruit Long Name],",") in Text.Combine( List.Transform(mylist, (x)=> Text.At(Text.Trim(x),0)) , ",")
Hey I've created you a sample PBIX file and a short video showing how I would do it. I'll pop the advanced code below for your information.
https://1drv.ms/u/s!AnIEh6WhI4Jogr5bZFX2r_p-u6P7sw?e=68SsBN
//Make a duplicate of origional column
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
//split the new column by delimiter
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2", type text}, {"Column1 - Copy.3", type text}}),
// extract first letter from each
#"Extracted First Characters" = Table.TransformColumns(#"Changed Type1", {{"Column1 - Copy.1", each Text.Start(_, 1), type text}, {"Column1 - Copy.2", each Text.Start(_, 1), type text}, {"Column1 - Copy.3", each Text.Start(_, 1), type text}}),
//add prefix to new column 1 ","
#"Added Prefix" = Table.TransformColumns(#"Extracted First Characters", {{"Column1 - Copy.2", each "," & _, type text}}),
//add prefix to new column 2 "," ....
#"Added Prefix1" = Table.TransformColumns(#"Added Prefix", {{"Column1 - Copy.3", each "," & _, type text}}),
//Merge newly created columns with the prefix " "
#"Merged Columns" = Table.CombineColumns(#"Added Prefix1",{"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Thanks for the response. But the problem with your code is. It wont work when there is a new fruit combination is added. for example, let's say I have published this report and later new record has been added to the data source column called "Apple, Banana, Plum, Mango". But my result set will show only "A, B, P". What I wanted is "A, B, P, M". The combinations changes often.
Try this custom column
=let mylist=Text.Split([Fruit Long Name],",") in Text.Combine( List.Transform(mylist, (x)=> Text.At(Text.Trim(x),0)) , ",")@Zubair_Muhammad It worked as expected. Thank you.
If the fruits added to your report are pear, peach, longan, and loquat, what will the short names look like after the merger?
My advice is to add a dictionary manually, as it is difficult to do automatic logical transformations using code alone.
