Divide columns

[Tomasavlis]

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?

[SundarRaj]

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!

[ronrsnfld · Accepted solution]

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:

[Tomasavlis]

 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]

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

[Tomasavlis]

Yes it does, thank you so much!

[ToddChitt]

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.