Forum Discussion

vidyasagar159's avatar
vidyasagar159
Helper II
5 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. dynam...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    5 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
5 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.