Power Query : Maximum Append Rows 5000 Rows

[muhammad susanto]

hi all..
i have 5 excel files with different name
i want to append all files with maximum rows allowed can appended e.g. max 5000 rows for each file
how to make formula in Power Query

thank for your helping

susanto

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=320960#p320960
https://eileenslounge.com/viewtopic.php?p=320957#p3209573
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[sandy666]

It is not clear.

If you want to display result as one table in a single sheet look at:
Total number of rows and columns on a worksheet : 1,048,576 rows by 16,384 columns

Power Query has no limits for rows and columns so explain what do you want to do with data from these 5 files

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[muhammad susanto]

i have problem for 5 files, 2 files can normal rows when appended but 3 files not normal i mean they appended till 1.048.576 but actually total rows only 5000 rows..
how to fix this problem?

[sandy666]

I still don't understand
answer for this:
1. how many rows you have for each file
2. do you want display the result in the Excel sheet (limited) or in Power Query (not limited)
3. you can load many files into Power Query but you can append queries in Power Query and see unlimited result or load result query table into the sheet with limitation as I said above

[muhammad susanto]

for 1 question, for each file aprox 3000 rows
i have 5 files actually for each file contains 3000 rows but while i process append file, only 3 files with normal rows insert approx 3000 rows for 2 files inserted row till 1,048,576 (the fact 3000 rows)
i want display in Excel sheet limited (approx 3000)
i just load 5 files excel with 1 sheet for each file

[sandy666]

5 x 3000 is 15 000 rows , limit is a bit over a 1 million rows
so I don't understand where is the problem
maybe data in the files are not compatible between them

[muhammad susanto]

hi sandy, my problem when i running power query so slowly for when append data 1 million rows...the fact, my data rows not reach 1 million just approx 3000 rows
i don't know what happen with my files...i have clear unnesesary rows and cleaning formatting
all my excel file in the same format..i'm using Excel 2013..
it happen only for 2 files the other file is normally ...it's mean if the file contains 3000 rows after append 3000 rows too..

[sandy666]

Hard to understand, but check for column names (must be exactly the same) .Power Query is case sensitive
Maybe check data types also
Excel 2013 using Power Query add-in and this is not the best choice. I suggest Excel 2016 or higher
On the end I suggest attach these 5 files. If there is sensitive information use generic data

[muhammad susanto]

hi sandy..here i attached 2 files that contains my problem above
file "test_batam" is contains problem
please, if you find what happen give me what's the real problem & how to fix it

[sandy666]

test_padang.xlsx
test_batam.xlsx
test.xlsx
You've a big mess in your files
You need to clean everything (unnecessary tables in Name Manager, data validation lists, etc.)