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
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
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
Last edited by DocAElstein; 09-22-2023 at 05:21 PM.
sandy
I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
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?
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
sandy
I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
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
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
sandy
I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
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..
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
sandy
I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
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
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.)
Last edited by sandy666; 01-20-2022 at 01:46 AM.
sandy
I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
Bookmarks