Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create new column from Data coming from Different columns

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 formu...
Need Help
v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

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.