Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have multiple text files I want to import into tables. They say it is ASCII format, but there are no delimiters. The way it was setup is the first row has a number dashes and then a space, the space indicates the number of characters for each column. My issue is I cannot figure out how to repeat this import without counting the columns each time and entering it in as a delimiter, for example - Csv.Document(File.Contents("C:\Users\Dean\Downloads\SPUD0507.txt"),null,{0, 20, 53, 63, 70, 100, 108, 132, 145, 151, 180, 194},null,1252).
Is there someone out there that has completed a task similar to this and is willing to show me how to do it? There is an example of the data
@dcasorso wrote:
I have multiple text files I want to import into tables. They say it is ASCII format, but there are no delimiters. The way it was setup is the first row has a number dashes and then a space, the space indicates the number of characters for each column. My issue is I cannot figure out how to repeat this import without counting the columns each time and entering it in as a delimiter, for example - Csv.Document(File.Contents("C:\Users\Dean\Downloads\SPUD0507.txt"),null,{0, 20, 53, 63, 70, 100, 108, 132, 145, 151, 180, 194},null,1252).
Is there someone out there that has completed a task similar to this and is willing to show me how to do it? There is an example of the data
I do think you should enter the {0, 20, 53, 63, 70, 100, 108, 132, 145, 151, 180, 194} to make things not complicated. However you don't have to count the columns. What you need is a good text editor, such as NotePad++. You'd get the position numbers when putting the input cursor before a space.
I'd try the following:
Select the first record (Source{0}) and add a column: Text.PositionOf([Column1], " ", Occurrence.All)
This should return a list with all positions of blanks. Fill in this stepname as a variable into the Csv.Document call (instead of the list with the hardcoded numbers)
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
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
44 | |
41 | |
39 | |
37 |