cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: How do I create a custom function in column 2

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
Highlighted
Super User III
Super User III

Re: How do I create a custom function in column 2

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

Highlighted
Helper II
Helper II

Re: How do I create a custom function in column 2

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.

Highlighted
Super User III
Super User III

Re: How do I create a custom function in column 2

@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
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021