Replace data with certain text

[Anonymous]

Hello,

I load data with in a column several text that can change over time. I need only the LE 01 until LE 18 from this:

 

What I am doing now is replacing text per text with a new text:

Then I delimite this column to extract the number, e.g. 13, from this:

 

  Since it is a lot of work to do this text per text and the text can change in the future, is there a better option? E.g. prevent loading this data? So only 'LE 01' until 'LE 18' is loaded in the first place? 

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I'd like to suggest you add a custom column with if statement to check if records which start with 'LE ', otherwise replace it as null.

Then you can use fill functions to 'fill up' text to replace null records.

Finally, you can add custom column with text.middle function to extract number from custom column 'replaced'.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5Ba4QwEIX/yuC5hplotHjbjSPIWgNRS0EksLClh9KFvfXfd9Qtuu0pk/e9eW/GMaosgUbKwDpIMadoehqjU/Jfq+o3LuGVfVe7VoH7+vwGe+XYdwtu0ATZyTHDLFCqnw0uOsl31sP58n69XUJlw6GsV6Qwf2DWec+2l/x7ZP53NUW1XtMw4G6CGCVNq9muAMgUxnzc9hwwE8PMgXRBKMqRK+cZDrZ/MC4XDtxtqt4mBTr5LRIvGa0LxI1DXLaDgF725bGu7YYX9tA2sDOtzfviJJqmHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Replaced", each if Text.Start([Type],3)="LE " then [Type] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Replaced"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Up", "Custom", each Text.Middle([Replaced],3,2))
in
    #"Added Custom1"

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft Thank you! This is indeed working for me.  Great pragmatic solution. thank you again. 

[PattemManohar]

@Anonymous Could you please provide a sample data to replicate your issue, so that it will be helpful to provide an accurate solution.