Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 |
Solved! Go to 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.
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
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.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.