Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Power Query : Maximum Append Rows 5000 Rows

  1. #1
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0

    Power Query : Maximum Append Rows 5000 Rows

    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
    Last edited by DocAElstein; 10-04-2024 at 10:33 PM.

  2. #2
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7
    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

  3. #3
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    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?

  4. #4
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7
    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

  5. #5
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    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

  6. #6
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7
    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

  7. #7
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    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..

  8. #8
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7
    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

  9. #9
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    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
    Attached Files Attached Files

  10. #10
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7

    Cool

    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

Similar Threads

  1. PQ - Power Query split?
    By sandy666 in forum Power Query, Power Pivot and Power BI
    Replies: 1
    Last Post: 04-06-2021, 07:51 PM
  2. PQ - Transforming Text to Date in Power Query
    By sandy666 in forum Power Query, Power Pivot and Power BI
    Replies: 2
    Last Post: 02-22-2021, 01:08 AM
  3. PQ - How to get Total via Power Query
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 09-23-2020, 06:10 PM
  4. PQ - UnZip an use Content in Power Query
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 05-13-2020, 05:37 AM
  5. Summary Of Maximum Rows Used Across Each Sheet In A Workbook
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 9
    Last Post: 09-04-2015, 07:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •