Combining two excel tables with over one million rows for Power BI

[kennedy311]

Hi everyone!

I'm hoping this is a simple fix, and that I just don't know the right phrase(s) to search for, here.

I have a data model in Power BI that is, for the short-term, running completely off of Excel spreadsheet exports. One of those spreadsheets is a list of CRM activities for each contact in the CRM over a period of time. I need the ability to append new activities to this table at the end of every month, but I have very quickly hit the one million row limit in Excel.

So my question is: using only spreadsheet exports from the CRM system, is there a way to create a table in Power BI/Power Query that will allow me to append new activity data at the end of each month? The main constraint is that for the time being, this data will be provided as a spreadsheet export only...so that's where I have to start.

I don't have a ton of Power Query experience, so maybe this is pretty straightforward?

Reach out with questions, and thank you!

[watkinnc]

If the issue is that you need to load millions of rows, you can do it in Excel, either by loading to the data model and using Power Pivot, or by loading the query directly to a pivot table report instead of a worksheet.

--Nate

[kennedy311]

Hi @BA_Pete ,

Thanks for this...how would this work if I am doing my initial modeling work in PBI Desktop? I have a version of this model published to the Power BI service. Do I just complete all the new modeling work I've done, upload a fresh version to the Service, then start the incremental refresh from there going forward?

[BA_Pete · Accepted solution]

Hi @kennedy311 ,

In brief:

Your initial modelling would be done on a limited (<1M rows) dataset, and your report would need to be in its final state when you publish.

Once published and incremental refresh (IR) is turned on, the dataset will then be able to grow incrementally in the cloud using periodic snapshots of data.

Once the IR process is started, it cannot be updated or downloaded, hence why your report needs to be in its final state and be able to stand the test of time before switching on.

The in-and-outs of how this should be set up will be quite specific to your scenario and not reasonably explainable here, hence the link.

It's not an ideal solution and may not be right for you/your scenario, but it's the closest to meeting your requirements that is fully supported within the Power BI Universe. Beyond this, I think you'd have to look at setting something similar up in a proper data warehouse.

Pete

[BA_Pete]

Hi @kennedy311 ,

It sounds like you want to use Incremental Refresh in the Power BI Service. Using this, you will only need to export a daily/weekly/monthly snapshot of transactions that have occurred in that time, and The Service will automatically add the new data onto a partitioned 'history' table.

Read more here:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

Pete