Excel Power Query Wont Load All Rows

[YWolf970]

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 Query shows that their are only 1218272 rows. I have not aplied any filter, you can see that in the screenshot.

I have also tried splitting the file, still it won't load all rows of data. How can I retrieve all data with Power Query. 

I am using Windows 10 64bit and Office 2019.

Link to maste file: https://drive.google.com/file/d/1sd6Jq-Wpux1WPgpvO1ciZrWdjGX7x3Fe/view?usp=sharing

Link to excel file (you will need to change source of master file for it to work): https://drive.google.com/file/d/1JXUe4zPaHdL4MqpMoR_gvU-NQ8lMAYQe/view?usp=sharing

Thanks for the help

[#2]

@YWolf970 

Maybe this information will help you.

Power Query specifications and limits

https://support.microsoft.com/en-us/office/power-query-specifications-and-limits-5fb2807c-1b16-4257-...

* It is always good to know which version of Excel is being used and which operating system is used.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#3]

@NikolinoDE using windows 10 64bit and office 2019. I will check out limitation link you shared but I don't think it's that, I have another file that has over 5 million records and it reads it perfectly 

[#4]

@YWolf970 

Do you know why Notepad++ shows line 1,218,272 in status bar if you stay on the last line of the file?

[#5]

@SergeiBaklan it shows 1218272 because it was active line, my curser was on this line. I was confirming if power query reads 1st 1218272 lines or randomly reads lines. Turns out it randomly reads rows of data as line 1218272 in notepad++ and 1218272 in powerquery data load to sheet are different (I splitted the records at 1 million rows so these 1218272 rows were loaded into 2 sheets within same workbook).

[#6]

@YWolf970 

But very left grey column in Notepad++ shows 2 479 724 - it shall be the number of current row on which you stay.

[#7]

@SergeiBaklan I used slider to get to the bottom, I will update the screenshot as soon I get home

[#8]

@YWolf970 Thank you, not necessary if you are sure. Your query is connection only or with loading to data model?

[#9]

@YWolf970 

If I may point this out to you and at the same time need your help.

I would like to ask you not to post the same question in many forums at the same time.

This leads to the fact that many people deal with your topic,

who could help other people at the same time.

Bear in mind that others may also need help and everyone who helps, helps on a volunteer basis.

https://stackoverflow.com/questions/64202606/power-query-will-not-load-all-rows

Thank you for your patience and understanding

Nikolino

I know I don't know anything (Socrates)

[#10]

@SergeiBaklan Its connection only and I am not loading to data model. I have another file that has about 500k records and I want to do inner join and load data to table. 

Attached below screenshot with last active line. I have also added link to the master file in the question. 

[#11]

@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?

[#12]

@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

[#13]

@NikolinoDE noted

[#14]

@YWolf970 

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