Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Split column with no clear delimiter

I need to split a column of data in two but there isn't any obvious delimiter nor uniform number of characters. Below in text and also linked is a sample of my data my split requirements.   1/1-4 N...
Need Help
  • kcantor's avatar
    kcantor
    7 years ago

    Anonymous 

    How about this, then?

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxCoMwFEV/5ZKphYT4NMY50KE4ODRuIUOgbxAEA9p8f1Ol6znn3hAEaVIG01Y+u4gyiE73igw8nktmnIhsbYgkiDxePzwzr6dq+5oPeKSyvHFz67Hd/5NOGQllHFxJ1Y3+MtY2Q71rWowpZ9Yz74eI8Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Column1],{"a".."z","A".."Z","(",")"})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom.2"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Length([Custom.1])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Left", each Text.Start([Column1], [Custom])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Right", each Text.End([Column1], Text.Length([Column1])-[Custom]))
    in
    #"Added Custom3"

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous ,

 

We can do part of steps in power query like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxCoMwFEV/5ZKphYT4NMY50KE4ODRuIUOgbxAEA9p8f1Ol6znn3hAEaVIG01Y+u4gyiE73igw8nktmnIhsbYgkiDxePzwzr6dq+5oPeKSyvHFz67Hd/5NOGQllHFxJ1Y3+MtY2Q71rWowpZ9Yz74eI8Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Column1],{"a".."z","A".."Z","(",")"})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom.2"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Length([Custom.1])+1),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}})
in
    #"Changed Type2"

Then we can create two calculated column based on it.

left = LEFT('Table'[Column1],'Table'[Custom])
right = RIGHT('Table'[Column1],LEN('Table'[Column1])-'Table'[Custom])

Anonymous's avatar
Anonymous
Not applicable
7 years ago

v-frfei-msft  Many thanks for the help.

 

I can manage the first part using the suggested M Code but I'm not too familiar with custom columns and I'm getting a Token Literal Expected error. When I click on Show Error it highlights the first single quotation mark on 'Table'[Column1].

 

What exactly does Token Literal mean in this instance?

 

 

  • kcantor's avatar
    kcantor
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 

    The second part appears to be building two calculated columns in PowerBi using Dax, not in the Query Editor using M. Load your query and attempt to make those columns in Dax.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      kcantor  Thanks, you're right. The second part is done with a calculated column using DAX and unfortunately I have to make this work in Excel.

       

      Can this be done either with calculated columns in Excel's Power Query or by modifying the M Code?

       

      Thanks

      • kcantor's avatar
        kcantor
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous 

        I have come in on the late side of this and was just explaing the erroryou were receiving. I do not have access to the sample data you provided.

        I am not sure what you mean that you need to do this in Excel. Do you mean Query Editor? PowerBI? PowerPivot?  

        Also, if you can grant access to the file, I would be happy to take a look but please be specific about your desired output software.