How do I create a custom function in column 2

[craig811]

Hi,

I have an account in column 2 in my table : 

054.051.0540000.0800000.056.1234.0000

Can I write a custom function to pull the fourth segment: 0800000 and replace the last three digits with segment five:056 so when I call the function it will provide me the value: 0800056 ?

Thank you 

[AlB · Accepted solution]

Hi @craig811 

1. Create a blank query

2. Open the advanced editor, delete the code shown and paste this code instead:

(input_ as text)=>
let 
    list_ = Text.Split(input_,"."),
    output_ = Text.Start(list_{3},Text.Length(list_{3}) - 3) & list_{4}
in
    output_

3. Name the query with the name you want for the function, for instance myFunction_

4. In your original query, add acustom column with the code (assuming Column2 is where you have the data you want to apply the function to)

= myFunction_([Column2])

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[craig811]

This is great , how can I make the value show in all of the rows under my new column? Currently it will just show in one.

[AlB]

@craig811 

What do you mean? are you just invoking the function with one value or are you creating a new custom column with the call to the function as I indicated earlier?  I need more details/pics to understand what you mean

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers