Power Query : Maximum Append Rows 5000 Rows

**muhammad susanto** · 01-19-2022, 12:01 PM

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

**sandy666** · 01-19-2022, 01:01 PM

**muhammad susanto** · 01-19-2022, 01:43 PM

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** · 01-19-2022, 01:58 PM

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** · 01-19-2022, 02:42 PM

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** · 01-19-2022, 02:46 PM

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** · 01-19-2022, 04:18 PM

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** · 01-19-2022, 04:26 PM

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** · 01-19-2022, 08:25 PM

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** · 01-20-2022, 01:41 AM

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.)