substituting null values with another column substring

[df123445]

I have the following raw data:

CODE	PRODUCT
PRD1	engine [AB-PRD1-XY]
PRD2	wing [AB-PRD2-WZ]
null	tail [AB-PRD3-TT]
null	window[AB-PRD4-AA]

as you can see the data is not complete, some rows  have no value in the CODE column, and I need to extract

it from the PRODUCT (But only for the null values) 

I guess the pseudo-logic should be:

if CODE=null  

CODE = find occurence of "AB-" in PRODUCT field and grab the following 4 characters   

Anyone could help me pu that in M language or point me in the right direction? TIA 

[ryan_mayu · Accepted solution]

you can try this

= Table.AddColumn(#"Changed Type", "Custom", each if [CODE] ="" then Text.Middle([PRODUCT] ,Text.PositionOf([PRODUCT],"AB-")+3,4) else [CODE])

[Anonymous · Accepted solution]

Here is one possible solution for your problem:

Using
- Text between delimiters
- Conditional column

A tip is to use the functions avaible in the transform pane:

, in order to specify which text you want to extraxt from the CODE column.

Applying the relevant steps automaticcaly generates the following solution code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghyMVTSUUrNS8/MS1WIdnTSBYnoRkTGKsXqgKWNgNIxJeWZeekwaSPd8CiINFCqJDEzByZhrBsSApcA6kjJL4fKmOg6OgJlYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CODE = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE", type text}, {"Product", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Product], "-", "-"), type text),
    #"Code adjusted" = Table.AddColumn(#"Inserted Text Between Delimiters", "Code adjusted", each if [CODE] = "" then [Text Between Delimiters] else [CODE]),
    #"Removed Columns" = Table.RemoveColumns(#"Code adjusted",{"Text Between Delimiters"})

in
    #"Removed Columns"

Kind regards

Mark as a solution if the answer solved your question 🙂

[ryan_mayu · Accepted solution]

you can try this

= Table.AddColumn(#"Changed Type", "Custom", each if [CODE] ="" then Text.Middle([PRODUCT] ,Text.PositionOf([PRODUCT],"AB-")+3,4) else [CODE])

[df123445]

thanks youuuuu!!!!!

[ryan_mayu]

you are welcome