Power Bi Merging of files of same format for consolidated data

Dhiren Shah 0 Reputation points
2026-08-26T05:02:30.27+00:00

Dear Sir/Madam,
I have my data stored in CSV file. However due to limitation, i can not paste additional row data after 10,48,576 line number. How i can merge 2nd work sheet data in fist CSV file which are having same format. So that i can avoid size limitation while uploading data in Power Bi application and to generate single dashboard report. Please guide since i have newly started using Power Bi application. Thank you.

Microsoft 365 and Office | Development | Other
0 comments

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-08-26T05:02:47.5+00:00

    Use Power Query to combine the CSV files into one table instead of trying to append rows inside a single CSV file.

    Recommended approach in Power BI Desktop:

    1. Put all CSV files that have the same structure into one folder.
    2. In Power BI Desktop, select Get data > File > Folder > Connect.
    3. Enter the folder path, select OK, then choose Transform data.
    4. In Power Query Editor, select Combine or Combine Files.
    5. In the Combine files dialog, keep the default settings if the files have the same format, then select Transform data.
    6. Power Query creates helper queries and combines the files into one output query.
    7. If the files contain extra header rows before the real column names, open Transform Sample file and apply the cleanup there. For example, if each file has four non-data rows at the top, use Remove top rows and enter 4. That transformation is then applied to every file before combining.
    8. After combining, set the correct data types for each column, then load the data.

    Important points:

    • This works best when all files have the same file type and the same column structure.
    • The combined output includes the source file name as a column, which helps verify that all files were included.
    • To confirm all files were merged, check the Source.Name column filter and use Load more if the list looks incomplete.
    • In the Power BI service, CSV files can be imported, but for combining multiple CSV files into one model, Power BI Desktop with Power Query is the supported path shown here.

    This avoids the worksheet row limit problem because the data is combined during import rather than pasted into one file.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Liora 1,630 Reputation points Independent Advisor
    2026-08-26T06:57:42.03+00:00

    Dear @Dhiren Shah,

    I hope you’re having a good day. 

    Thank you for your question, and welcome to Power BI.

    From your description, it appears that you are encountering Excel's maximum worksheet limit of 1,048,576 rows. If your data is split across multiple CSV files with the same structure, you do not need to combine everything into a single file before importing into Power BI.

    Instead, Power BI can automatically combine multiple CSV files into one dataset.

    Option 1: Import all CSV files from a folder

    1. Save all CSV files into the same folder.
    2. Open Power BI Desktop.
    3. Select: Home > Get Data > Folder

    Screenshot of the Get Data dialog box highlighting the choice to connect to a folder.

    1. Browse to the folder containing your CSV files.
    2. Click OK.
    3. A list of files will appear. Select: Combine > Combine & Transform Data
    4. Power BI will automatically:
      • Read all CSV files.
      • Append the rows together.
      • Create a single consolidated table.
    5. Click Close & Apply.

    Your report can then use the combined dataset as if it came from a single file.

    Option 2: Append two files manually

    If you already imported the files separately:

    1. Load both CSV files into Power BI.
    2. Open Transform Data.
    3. In Power Query, select: Home > Append Queries
    4. Select:
      • Table 1 = First CSV
      • Table 2 = Second CSV
    5. Click OK.

    Power BI will merge the rows from both files into one table.

    I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.   

    Looking forward to hearing back from you with any updates or additional details.  

    Warm regards,  

    If the answer is helpful, please select "Yes, accept answer". If you have any questions or new updates, please feel free to comment below. 

    Note: Please follow the steps in the forum documentation to enable email notifications if you want to receive the related email notification for this thread.  

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.