Split Column Automatically based on a condition

[JulianaMacedo]

Hi everyone!

I need to split a columns and name it automatically, I've done a couple of tries but I'm getting some errors due to the type that I'm using and I've tried to convert the type, but have been unsuccessfully.

I have a column that looks like this (it's binary numbers in format of string):

Binary

100100000000000000000000000000000000000

100000000000000000000000000000000000000

100100000000000000000000000000000000000

I want to take this columns and split it for every 8 digitis and create a new columns for each one of them. I did it previously using the built-in function "Split Columns by position" and worked perfectly, but then I have to pre-determine the number and name of the columns. 
What I want to do now is to write a function that will split the columns automatically and name them automatically as well.

This is what I have been trying: 

(sourcecolumn as text) =>
let
n_bytes = Number.RoundUp(Text.Length(sourcecolumn)/8),
listofcolumns = List.Generate(
    () => 0, //value to start the series with
    each _ < n_bytes, //as long as condition is true create series
    each _ +1  //the value to create next in each step
    )
in
Table.SplitColumn(sourcecolumn, Splitter.SplitTextByRepeatedLengths(8),listofcolumns)

When I try to invoke the function giving the first binary string I get an error:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "10010000000000000000..." to type Table.
Details:
    Value=100100000000000000000000000000000000000
    Type=[Type]

I'm generating a list to automatically give me the number of the columns, which by the way I would like to add the word "Byte" before the number, so the result should be:

Binary	Byte0	Byte1	Byte2	Byte3	Byte4
100100000000000000000000000000000000000	10010000	00000000	00000000	00000000	0000000
etc

Anyone has any idea how to make it work?

[JulianaMacedo]

Hi!
Yeah the result you showed is what I'm looking for, although I haven't been able to make your solution work yet... I'm working on it. I tried to apply the way it is but it is giving me an error, so I'm now trying to figure out how to use it.
The error that I'm seeing is:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "10010000000000000000..." to type Table.
Details:
Value=100100000000000000000000000000000000000
Type=[Type]

[KT_Bsmart2gethe]

Mind sharing your code?

[JulianaMacedo]

I have tried to below code in attempt to make it split into the right number of colums, but it is not working either and I can't understand why:

= (source as table, column)=>
let
n_bytes = Number.RoundUp(Text.Length(column)/8),
split = Table.SplitColumn(source, column,Splitter.SplitTextByRepeatedLengths(8), n_bytes)
in
split

[JulianaMacedo]

Hi!

I literally copied the second option and input the values when adding a column using invoke function.

But now I'm testing the first part of the code and it is not giving me the result I need, it's incomplete. 

When doing this (I'm just testing the Table.SplitColumn to see if it is spliting in the rigth number of columns):

When I input the table and the column I want to split and I'm getting max 5 columns, when I should be seeing 7 in total.

When the string (binary number) has the length of 53 for example, if splitting it in pieces of 8 it should bring 7 columns and it is not.

[KT_Bsmart2gethe]

When you write the code:

Table.SplitColumn(source, column,Splitter.SplitTextByRepeatedLengths(8), n_bytes)

You can ignore the syntax ", n_bytes". It will spill the columns for you.

After the split step, we need to rename those split columns.

From there, dynamically change the column name, you can use the rename code I provided. 

Can you do the Text.Length() to the original column (i.e. the column before split?) just for checking. It is not easy to visualise the problem in my mind without looking at the code.

[JulianaMacedo · Accepted solution]

I have tried ignoring the sintax ",n_bytes", this was actually the first thing I tried. I created that in order to see if this would give me the correct result. Trying without the sintax it is not giving me the correct result because it does split but to a limit number of columns, I need to have 7 columns and it gives max 5 columns.
And yes, if I apply the Tex.Length()in the original columns works fine.
Anyway, I did find another way to do it that is working. 
Thanks a lot for your time anyway, it's is really precious to have these discussions.

[KT_Bsmart2gethe]

Hi @JulianaMacedo ,

Is this what you after?

Method 1 - Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwACKCQClWB6yWGABTS5y5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Binary = _t]),
#"Split Column by Position" = Table.SplitColumn(Source, "Binary", Splitter.SplitTextByRepeatedLengths(8)),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Position",Table.ToRows(Table.AddColumn(Table.FromList(Table.ColumnNames(#"Split Column by Position")),"Column2", each Text.Replace([Column1],"Binary.","Byte"))))
in
#"Renamed Columns"

Method 2 - code (Turn the above code into function):

(PrevStep, ColumnName,NewColumnName)=>
let
Split = Table.SplitColumn(PrevStep, ColumnName, Splitter.SplitTextByRepeatedLengths(8)),
Rename = Table.RenameColumns(Split,Table.ToRows(Table.AddColumn(Table.FromList(Table.ColumnNames(Split)),"Column2", each Text.Replace([Column1],ColumnName&".",NewColumnName))))
in
Rename

Regards

KT