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 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:

 

JobJob1Job2Job3Clean_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
   3245LU3245
   2456LU2456
  Purchase Order #LU5958-8 LU5958
     
Need Help

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.

  • 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.