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
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.
Can anyone advise me? I can't solve it. Thank you very much
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you very much. It worked! 🙂
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |