Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Split column number and text to different column with custom logic

Hi Experts,

 

I need your support on how to split the column from left to column on right. please help to advise on how can we do it at the power query level.

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Use below M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCok0NDA0MDMwMFCK1YlWgnF0DVG4SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Data", "Data - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Data.1", "Data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Text.Remove([Data.1],{"A".."z"})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.Max(Table.SelectRows(#"Added Custom1",each [Custom.1]=[Custom.1]),"Data.1")),
    #"Expanded Custom.2" = Table.ExpandRecordColumn(#"Added Custom2", "Custom.2", {"Data.1"}, {"Custom.2.Data.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Data.1", "Data.2", "Custom.1"})
in
    #"Removed Columns"

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    You can perform the following steps on your Data column

     

    Text before delimiter.  Put - in the pop up box for the delimiter.

    Replace Values  Put TY in the top box and nothing in the bottom.

    Add Prefix of TY

     

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Pat,

      i am not sure on how to do this and I am newbie to prower query. i am looking for M code for this.

       

      Srini

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I bet Column From Examples would work. 

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  Anonymous ,

     

    Use below M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCok0NDA0MDMwMFCK1YlWgnF0DVG4SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Data", "Data - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Data.1", "Data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Text.Remove([Data.1],{"A".."z"})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.Max(Table.SelectRows(#"Added Custom1",each [Custom.1]=[Custom.1]),"Data.1")),
    #"Expanded Custom.2" = Table.ExpandRecordColumn(#"Added Custom2", "Custom.2", {"Data.1"}, {"Custom.2.Data.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Data.1", "Data.2", "Custom.1"})
in
    #"Removed Columns"

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!