Forum Discussion

Tomasavlis's avatar
Tomasavlis
Regular Visitor
1 year ago
Solved

Divide columns

Hi!

 

I have a column with numbers with either 4 or 7 characters, how can I divide the column into the ones with 4 and the ones with 7?

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    Please provide sample data and expected outcome from that data.

    There are many ways to accomplish this and variations depend on the nature of your data and expected outcome.

     

    For example, if your data looks like:

    This code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlGK1YEwTM3MwWwQE84wM7cAs0FMOMPcwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", Int64.Type}}),
    
    Split = Table.FromColumns(
        {Table.SelectRows(#"Changed Type", each Text.Length(Text.From([Column]))=4)[Column],
         Table.SelectRows(#"Changed Type", each Text.Length(Text.From([Column]))=7)[Column]},
         type table[Four=Int64.Type,Seven=Int64.Type])
in
    #"Split"

     

    would produce:

     

     

     

6 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    1 year ago

    You would need to research the Power Query exact logic functions to use, but you could go off the length of the string. Create two calculated columns:

    Column Four digits = IF Length of original column = 4 then original column, else null.

  • Tomasavlis's avatar
    Tomasavlis
    Regular Visitor
    1 year ago

     Something like this, column with random numbers with no rule, just there are some with 7 and others with 4 digits. Although in the picture it's in between, in the real data set it's random

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      1 year ago

      Seems like my answer below does what you are looking for.

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    Please provide sample data and expected outcome from that data.

    There are many ways to accomplish this and variations depend on the nature of your data and expected outcome.

     

    For example, if your data looks like:

    This code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlGK1YEwTM3MwWwQE84wM7cAs0FMOMPcwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", Int64.Type}}),
    
    Split = Table.FromColumns(
        {Table.SelectRows(#"Changed Type", each Text.Length(Text.From([Column]))=4)[Column],
         Table.SelectRows(#"Changed Type", each Text.Length(Text.From([Column]))=7)[Column]},
         type table[Four=Int64.Type,Seven=Int64.Type])
in
    #"Split"

     

    would produce:

     

     

     

  • SundarRaj's avatar
    SundarRaj
    Super User
    1 year ago

    Hi Tomasavlis , here's a quick way to solve this problem. Do have a look. I'm attaching two images, one for output and second for the M code. Thanks!