Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
craig811
Helper III
Helper III

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 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

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 

SU18_powerbi_badge

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.

@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 

SU18_powerbi_badge

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors