Forum Discussion
How do I create a custom function in column 2
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
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
3 Replies
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
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.
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
