Data Modelling

[Chaitanya_malir]

Hello Guys,

I am new to power BI. I have .txt files in my folder whose data format is like this

i want to split this into three each as one row.for now i have done it using no .of characters. But the problem  is since all the text files in the folder are not same length . i am getting lets say Y columns . but for other text file i am getting X columns . 

But i want this to work dynamically . how to do it? can any one help me with this

Here i am attaching the data.

Thanks in Advance!

txt file1

00,00,1A,00,01,00,00,02,00,00,03,00,00,04,00,00,05,05,00,06,E4,00,07,1A,00,08,00,00,09,00,00,0A,00,00,0B,00,00,0C,05,00,0D,0A,00,0E,1A,00,0F,00,00,10,00,00,11,00,00,12,00,00,13,05,00,14,0A,00,15,1A,00,16,00,00,17,00,00,18,00,00,19,00,00,1A,05,00,1B,0A,00,1C,0B,00,1D,00,00,1E,00,00,1F,00,00,20,00,00,21,00,00,22,00,00,23,00,00,24,00,00,25,00,00,26,0B,00,27,00,00,28,00,00,29,00,00,2A,00,00,2B,00,00,2C,E4,00,2D,00,00,2E,E4,00,2F,00,00,30,00,00,31,00,00,32,00,00,33,00,00,34,00,00,35,00,00,36,00,00,37,E4,00,38,00,00,39,E4,00,3A,00,00,3B,E4,00,3C,00,00,3D,00,00,3E,00,00,3F,00,00,40,00,00,41,E4,00,42,00,00,43,00,00,44,00,00,45,E4,00,46,E4,00,47,E4,00,48,E4,00,49,14,00,4A,0A,00,4B,0A,00,4C,E4,00,4D,0A,00,4E,00,00,4F,00,00,50,00,00,51,00,00,52,00,00,53,00,00,54,00,00,55,00,00,56,00,00,57,00,00,58,E4,00,59,06,00,5A,E4,00,5B,00,00,5C,0A,00,5D,0A,00,5E,00,00,5F,00,00,60,00,00,61,0A,00,62,00,00,63,E4,00,64,E4,00,65,32,00,66,00,00,67,00,00,68,00,00,69,00,00,6A,00,00,6B,00,00,6C,00,00,6D,00,00,6E,00,00,6F,00,00,70,50,00,7...

txt file 2

00,00,1A,00,01,00,00,02,00,00,03,00,00,04,00,00,05,05,00,06,E4,00,07,1A,00,08,00,00,09,00,00,0A,00,00,0B,00,00,0C,05,00,0D,0A,00,0E,1A,00,0F,00,00,10,00,00,11,00,00,12,00,00,13,05,00,14,0A,00,15,1A,00,16,00,00,17,00,00,18,00,00,19,00,00,1A,05,00,1B,0A,00,1C,0B,00,1D,00,00,1E,00,00,1F,00,00,20,00,00,21,00,00,22,00,00,23,00,00,24,00,00,25,00,00,26,0B,00,27,00,00,28,00,00,29,00,00,2A,00,00,2B,00,00,2C,E4,00,2D,00,00,2E,E4,00,2F,00,00,30,00,00,31,00,00,32,00,00,33,00,00,34,00,00,35,00,00,36,00,00,37,E4,00,38,00,00,39,E4,00,3A,00,00,3B,E4,00,3C,00,00,3D,00,00,3E,00,00,3F,00,00,40,00,00,41,E4,00,42,00,00,43,00,00,44

[ImkeF · Accepted solution]

Hi @Chaitanya_malir ,
try splitting the column by number of characters:

Then delete the part in the M-code that defines the new column names, so that the resulting formula just looks like so:

Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByRepeatedLengths(9))

Alternatively, you could just add a step manually to your existing data and enter the formula above. Then adjust the first parameter the the name of the previous step in your query and the second parameter to the column name with the existing values.