Not all rows imported by Power Query from a fixed-width text file

[LucAppelmans]

I have a customer which has an issue related to Power Query. Let me explain the background:

Today they import a fixed-width text file into Access. Then they import the data from Access into Power BI using Power Query. Of course, this doesn’t make sense. Therefore the customer wants to directly import the fixed-width text file in Power BI. Amazingly when they import the fixed-width file not all rows are imported in Power BI. What is also very annoying is that there is no message. Just by checking the number of rows this was noticed.

Technical information:

• Text File Size: 591MB
• Number of rows: 929434
• Number of columns: 77

In Power BI only 470208 rows are imported. I have done several tests:

• I converted the fixed-width text file into a CSV file, and then all rows did import without any problem.
• I modified the fixed-width text file by only keeping 25 columns, then 50 columns, 60 columns etc. In all these cases all rows were imported. But when I have 64 columns then less rows are imported.
• I also tried to import all rows as one column and then use split column. Same result not all rows imported.

Is there any possibility to enable the import of the fixed-width file. What is surprising for me is that Access (a pretty old product) the file correctly imports (without any problem and fast), and that the “modern” Power Query cannot. Any ideas?

Here is the very simple M-code:

let
    Source = Csv.Document(File.Contents("C:\...\FILE.TXT"),77,{0, 3, 11, 19, 23, 26, 28, 29, 34, 52, 67, 82, 83, 86, 87, 88, 99, 110, 111, 118, 136, 141, 156, 158, 161, 178, 182, 186, 191, 196, 197, 215, 226, 227, 228, 229, 242, 245, 247, 253, 271, 277, 295, 301, 319, 325, 343, 349, 367, 373, 375, 378, 379, 394, 409, 411, 414, 427, 433, 445, 446, 447, 450, 455, 458, 484, 515, 530, 545, 560, 575, 605, 635, 636, 637, 640, 641},ExtraValues.Ignore,1252),
in
    Source

[Anonymous]

What's that list of numbers after the columns parameter (77)? Is that a list of column names, or delimiters or something? Seems wrong.

--Nate
 

[AlienSx · Accepted solution]

@Anonymous looks like positions in fixed-width text. But this parameter should be

• delimiter can be a single character, a list of characters, or the value "", which indicates rows should be split by consecutive whitespace characters. Default: ",".

@LucAppelmans i would try to use Lines.FromBinary instead of Csv.Document and then split text by positions. Just my 2 cents.

[LucAppelmans]

Hi AlienSx,
I tried your suggestion to use Lines.FromBinary, and later split the column.
I got all my rows. Great thanks.
Do you have any explanation why this did work?
Anyway Great Thanks

[AlienSx]

Hello, @LucAppelmans to be honest I don't know why Csv.Document failed in your case. I can't find split by positions option here however PQ interface does allow to enter positions (smth new to me).  Anyway I am glad Lines.FromBinary solved your problem. It worked because it is desined to "convert a binary value to a list of text values split at lines breaks". At least you get all your lines. 🥂

[LucAppelmans]

Anyway thanks for your feedback and help.