Create new column from Data coming from Different columns

[Anonymous]

Hi Experts, 

I would like to create a column in power query picking up data from other columns on which some columns the Data format is ready but for other columns I have to use search/find formulas and also concatenate. The Job data always have the first 2 character as Letters e.g. "LU" then followed by four digits. The source data can come from 2 columns, which if the 4 columns are blank would like to put "null" in the modified column. Table below shows and example and what i would like to achieve:

Job	Job1	Job2	Job3	Clean_Column
LU6647				LU6647
LU6573				LU6573
LU7062				LU7062
LU6647				LU6647
				Null
	LU6867			LU6867
	LU6571			LU6571
	LU7478			LU7478
	LU6851			LU6851
				Null
		Purchase Order #LU6691-2		LU6691
		Purchase Order #LU6341-5		LU6341
				Null
			3245	LU3245
			2456	LU2456
		Purchase Order #LU5958-8		LU5958

[v-yalanwu-msft]

Hi, @Anonymous ;

Another method, you could create  a custom column:

= Table.AddColumn(#"Trimmed Text", "Custom", each if [Merged] ="" then "Null" else "LU"& Text.Select([Merged],{"0".."9","-"}))

then 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gk1MzMxV9JRUoDjWB2wsKm5MRZhcwMzI2yqsRmigFUEqNjCzBybsKm5IRZhcxNzC6yGmGKqxm5/QGlRckZicaqCf1FKapGCMsi5loa6RsQoNDYx1DUlwg4QNjYyMcUQBIqZEbDE1NLUQhfix9hYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Job = _t, Job1 = _t, Job2 = _t, Job3 = _t]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Job3", type text}}, "en-IN"),{"Job", "Job1", "Job2", "Job3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", Text.Trim, type text}}),
    #"Added Custom1" = Table.AddColumn(#"Trimmed Text", "Custom", each if [Merged] ="" then "Null" else "LU"& Text.Select([Merged],{"0".."9","-"})),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Added Custom1", {{"Custom", each Text.BeforeDelimiter(_, "-"), type text}})
in
    #"Extracted Text Before Delimiter"

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job", type text}, {"Job1", type text}, {"Job2", type text}, {"Job3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Job3", type text}}, "en-IN"),{"Job", "Job1", "Job2", "Job3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each if Text.Start([Merged],2)="LU" then [Merged] else if Value.Is(Value.FromText([Merged]), type number) then "LU"&[Merged] else Text.BetweenDelimiters([Merged], "#", "-"))
in
    #"Added Custom"

Hope this helps.

[Ashish_Mathur]

Hi,

The second and third last entries do not follow the rules that you have set.  There is no LU there in the Job3 column.

[Anonymous]

Hi Ashish,

The data are generated like this, thats why in excel i use this formula :

MID(Data[@[Labour Contract]],FIND("#",Data[@[Labour Contract]])+1,(FIND("-",Data[@[Labour Contract]]))-FIND("#",Data[@[Labour Contract]])-1) just to extract he Characters: LU####. 

The same with Job3 :"LU"& [Job3].

Thank you.