Replace character only at the beginning of string using Power Query

[nbkviou]

Hi, 

I'm trying to figure the M code to replace characters at the beginning of a string in Power Query.  For example,

AMP19336713

AMP5541921

AMP65534531

I want to replace the first 3 characters "AMP" with blanks so it reads instead 

19336713

5541921

65534531

What is an example of code to help me achieve this?

Thank you

[RMDNA]

@nbkviou,

If the "AMP" is consistent, you can use "Replace Values" and set AMP to blank.

If the AMP is not consistent, you can just cut the first 3 characters off the column value

[nbkviou]

AMP isn't consistent throughout the file. It can be in different positions. I've used the replace button option before when it was consistent. I'm trying to remove the first 3 characters. I have found code to remove characters except it does it at the end. I need it to do it at the beginning. This is the code i found.

=Table.TransformColumns(previous step, {{“Column to Appy”, each if Text.StartsWith(“AMP”)

Then Text.Start(_,Text.Length(_)-3) else _,}})

[ChrisMendoza]

@nbkviou,

Try Text.RemoveRange ( ) to remove the first 3 characters no matter 'AMP' or etc. :

=Text.RemoveRange([Column1],0,3)

[nbkviou]

I'm not trying to create a custom column. I'm trying to do it on the actual column. I don't think that will work cause that will remove the first 3 characters of every ID and its not consistent throughout the file. It can be 3 letters, 4 letters, or 5 letters. The code i mentioned above works except it takes off the last 3 characters as opposed to the first 3. That's why i've been trying to figure out.

[RMDNA]

There needs to be at least some consistency in formatting to be able to do this. Power BI can't be told what to remove if it could be anything in any location from one record to the next.

Are the characters you want to remove always letters, and the rest of the field always numbers?

Additionally, what's the difference between doing it on the column and creating a custom column? You can just remove the original column and name the new one the same - identical end result.

[nbkviou]

Hi,

I want to remove the letters at the beginning of the number. They could start with 3 letters, 4 letters, or 5 letters. I'm going to specify in the code the letters that i want to cut off. AMP is an example. I don't want to do a custom column cause that will cause alot of extra columns and be less efficient. I have the code i need to do this but it takes off the characters at the end opposed to the beginning. I'm trying to figure out how to adjust the code below

'This takes the last 3 characters off instead of the beginning'

=Table.TransformColumns(previous step, {{“Column to Appy”, each if Text.StartsWith(“AMP”)

Then Text.Start(_,Text.Length(_)-3) else _,}})

[ChrisMendoza]

@nbkviou,

This is probably a better solution for you since your data is not consistent like @RMDNA pointed out.

https://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/

Looks like @ImkeF saves the day again 🙂 

[nbkviou]

Thanks. This is good. It gives me an idea of something else i was trying to figure out. There's one issue i have trying to use this is that will remove all the letters which except the first 3,4, or 5. I'm dealing with part numbers for a company for there is letter in the middle and end of the number i need to keep. But the letters at the beginning of the part number are abbreviations for different company names which i'm trying to replace. For example,

Part Number                    Desired Number

AMP45789A                     45789A

BECI71364579F                71364579F

The consistency won't be a problem cause i'll specify myself what the beginning characters are and the length they are. I've just been racking my brain how to adjust the code i found to remove the beginning letters only.

[ChrisMendoza · Accepted solution]

@nbkviou,

This shows the solution I believe you are after where:

1. Find N alpha characters at the beginning of a text string
2. Remove N alpha characters from the beginning of the text string

The steps are shown and left for the education of others who may have a similar need.

    TextToList = Table.AddColumn(#"Changed Type", "PartNumberToList", each Text.ToList([Part Number])),
    FindNTextChars = Table.AddColumn(TextToList, "FindNTextChars", each List.FirstN([PartNumberToList], each Value.Is(Value.FromText(_), type text))),
    TrimmedStart = Table.AddColumn(FindNTextChars, "TrimStart", each Text.TrimStart([Part Number],[FindNTextChars]))
in
    TrimmedStart

Thank you to @v-shex-msft for your solution at https://community.powerbi.com/t5/Desktop/PowerQuery-Extract-Numbers-from-A-string-eg-ABCD1234/m-p/19... as it helped me in this solution.

[nbkviou]

Thanks for the response this worked. I just had one extra question to ask. I was trying to create a list of prefixes that i wanted to remove.  

Prefix_List

KR-

AMPS-

ADC-

COMM

COM

Reported_Client_Sku_Code

AMPS-0-1710731-2

AMPS-1-1710733-1

KR-66452780-07

KR-66452783-07

KR-66452787-07

For example, So all Reported_Client_Sku_Code's that have KR- would be removed. 

I came up with the following code but i think i'm missing something cause i get an error that the list doesn't match any rows in the table. I think there is a little something that i'm missing from my code. The List Table name is "Prefix_List."  The Table that contains Reported_Client_Sku_Code is "Exceptions_Raw_Data_Formatted_Output".

 

let 

    Source = Excel.CurrentWorkbook(){[Name="Exceptions_Raw_Data_Formatted_Output"]}[Content],

    PrefixList = Source{[Name="Prefix_List"]}[Content],

    #"Added Custom" = Table.AddColumn(PrefixList,"Result", each Text.Remove([Reported_Client_Sku_Code],PrefixList))

in

    #"Added Custom"

[nbkviou]

Thank you. This is great. Exactly what i was looking for. I'm new to M and still trying to learn the language. Do you have any good resources or suggestions to learn the M language?

[RMDNA]

Hi @nbkviou,

Glad to hear your issue was solved. Here's a couple good resources, and I'm sure others can add many more.

MSDN's Power Query M Reference site is the best place to learn the basics of M, syntax, and functions.

@ImkeFis probably our top Power Query expert on this board, and her site has one of the best collections of M resources.

[ChrisMendoza]

These types of details are necessary for the community to attempt to assist you.

In the future, I hope you will work towards providing details that would affect the solution.