Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply

Data Modelling

Hello Guys,

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

Chaitanya_malir_0-1667360467465.png

 

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 . 

Chaitanya_malir_1-1667360811699.png

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 file100,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 200,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
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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

ImkeF_0-1667364494003.png

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Super User
Super User

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

ImkeF_0-1667364494003.png

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.