Forum Discussion

dr_anas's avatar
dr_anas
Frequent Visitor
2 years ago

Excell raw limitation

I have a sales sheet whose data could be imported into an Excel sheet; however, the number of raw data points exceeds 2 million, and Excel has a limit of one million raw points. Although I can perform extraction quarterly, this would require me to transform the sheet in Power Query each quarter. Is there a way to automate this process for each new sheet added to Power BI?

Need Help

1 Reply

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi dr_anas ,

    • After connecting to an Excel file and selecting a table, go back to the Source applied step and you willl see all available tables.  Delete all steps other than Source.
    • Go to Kind column, select Sheet (or Table depending on your use case) and in the Name column select only those that you need.
    • If you select Sheet, the row headers will always be Column1, Column2, etc. so you need to create a custom column to promote the first row as headers. Use this formula: Table.PromoteHeaders([Data], [PromoteAllScalars = true])
    • You don't need to do that step above if you select Table as the headers will always be the first row of a table.
    • You may or may not delete the other columns other than the newly created one. My preference is to keep the name of the table which is either in Data or Item column so I'll know which tab or table a row in my loaded data is from.
    • Expand the newly created custom column and apply the necessary transformations