The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
109 | |
67 | |
46 | |
41 |
User | Count |
---|---|
186 | |
85 | |
76 | |
74 | |
56 |