Forum Discussion

vidyasagar159's avatar
vidyasagar159
Helper II
6 years ago
Solved

Conditional Column on dynamical Combination delimiters values

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    @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))
,
",")

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      vidyasagar159
      Helper II
      6 years ago

      @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.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        6 years ago

        @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))
,
",")
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.