Excel Power Query Wont Load All Rows

I have a large file that has 24,79,724 rows of data, but Power Query loads only 12,18,272 Rows, any one knows how to fix this? Below screenshot of Notepad++ shows all the rows. While Power...

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Oct 05, 2020

YWolf970

Thank you for the screenshot. I tried to play with about 2M rows text file, but with only couple of columns - PQ loads everything, into data model as well.

Thus that's not just number of rows, some combination. Did you try to clean the cache in PQ?

YWolf970

Copper Contributor

Oct 05, 2020

SergeiBaklan I have, I have also tried by increasing virtual memory.

NikolinoDE

Good News is that I have found the cause of the problem. Apparently its the m-code or you can say the fixed length definition I am using. If I remove it and import data as 1 column then it counts correct number of rows, screenshot is attached.

Here is the code, if I remove it then Power Query counts correct rows, It will be a big help if we can fix it as I need the coumns defined by length.

let
    Source = Csv.Document(File.Contents("C:\Users\Ali\Desktop\New folder\TaxRoll20200925\Master.dat"),41,{0, 11, 30, 33, 35, 65, 95, 125, 155, 168, 198, 223, 230, 236, 242, 246, 256, 286, 306, 326, 356, 386, 416, 446, 476, 496, 514, 524, 534, 540, 546, 556, 571, 581, 611, 626, 637, 648, 660, 672, 684, 699, 729, 739},ExtraValues.Ignore,20127)
in
    Source

SergeiBaklan
Diamond Contributor
Oct 05, 2020
YWolf970

So far so good, thanks for sharing. Bit strange for me, will try to play with that some later.

Forum Discussion

Excel Power Query Wont Load All Rows

Resources