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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Simon_29
Helper II
Helper II

Function for finding a letter in a number and then changing it to a certain value

Hello,

I have a table like this with the Order Number column.1. This column contains numbers that sometimes contain the letters - a, b, c, d, e, f, g .... I would need to find these letters and exchange them for numbers: a = 1 b = 2 c = 3 ... I can find and change one value by right-clicking on the column header and selecting "Replace Values", but I would need a code to find and change multiple letters at once. For example, in the picture below we see the number: 310638736GAO7, where I need to change the letters G, A, 0 after the numbers - 7, 1, 10.

bug.png


Can anyone advise me? I can't solve it. Thank you very much

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Here is a custom function that does that.  Create a blank query, open Advanced Editor and replace the text there with the code below.  In your original query, you can then go to the Add Column tab, invoke custom function and choose this function and choose your OrderNumber column as the input.

// fnReplaceLetters
(inputtext as text) =>
let 
Result = Text.Combine(List.ReplaceMatchingItems(Text.ToList(inputtext), List.Zip({{"A".."Z"}, List.Transform({1..26}, each Number.ToText(_))})), "")
in 
Result

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
mahoneypat
Microsoft Employee
Microsoft Employee

Here is a custom function that does that.  Create a blank query, open Advanced Editor and replace the text there with the code below.  In your original query, you can then go to the Add Column tab, invoke custom function and choose this function and choose your OrderNumber column as the input.

// fnReplaceLetters
(inputtext as text) =>
let 
Result = Text.Combine(List.ReplaceMatchingItems(Text.ToList(inputtext), List.Zip({{"A".."Z"}, List.Transform({1..26}, each Number.ToText(_))})), "")
in 
Result

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you very much. It worked! 🙂 

 

Gabriel_Walkman
Continued Contributor
Continued Contributor

Before addressing your issue, I'd like to point out that with your system, the converted order numbers can cause issues:

Letter K = 11
Letters AA = 11

You see the problem?

right now I realized that I would not have such an example of having two identical letters next to each other, so it would be enough if one letter finds and changes it.

It was just an example. The problem does not require identical letters. Again, just an example:

 

L = 12

AB = 12

Hi,
yes yes, I understand what would probably happen but then how can it be done? I can't believe that no one has tried such a thing yet 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors