Conditional Column on dynamical Combination delimiters values

[vidyasagar159]

Hi All,

Need some help. I am trying to achieve the below requirement. By using if else condition I can achieve the below result set. However, the combinations of the "Fruit Long Name" changes. dynamically. I don't want to update my condition every time the combination of the data changes. I am looking for some automation when there is new fruit combinations are added then fruit short names should have the short names with "," delimiters. 

Thanks in Advance!!

-Vidya

[v-alq-msft]

Hi, @vidyasagar159 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a blank query with the following codes in 'Advanced Editor' to define a custom function.

let
    Source = (input) => let
            values = {
            {"Apple", "A"},
            {"Banana", "B"},
            {"Plum", "P"}
            },
            Result = List.First(List.Select(values, each _{0}=input)){1}
            in
            Result
in
    Source

Then you need to add a custom column for 'Table' with following codes.

Text.Combine( List.Transform( Text.Split([Fruit Long Name],", "),each Query1(_)  ),",")

If you have more fruits and corresponding short name later, you may add them in the 'Query1'. Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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"

[vidyasagar159]

@Karlos

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.

[ziying35]

@vidyasagar159 

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.

[Zubair_Muhammad · Accepted solution]

@vidyasagar159

Try this custom column

=let mylist=Text.Split([Fruit Long Name],",")
in
Text.Combine(
List.Transform(mylist, (x)=> Text.At(Text.Trim(x),0))
,
",")

[vidyasagar159]

@Zubair_Muhammad It worked as expected. Thank you.