Power Query Data Unable to Load into PowerBI Model

[han-solo]

Hi there

I have multiple queries with timeseries data of multiple years which I want to load into a data model of PowerBI. When pressing "close and apply" and changing to PowerBI, the loading phase is unsuccessfull by means of the data model dashboard is never created. I assume that it's just to many lines of data in the table to handle it. 

Now my question is: Is there a way how to load data incrementally into a data model and then amend the previous data model with new data? Hence, I would want to lower the amount of data which would need to be loaded at once by splitting the data load into smaller chunks?

thank you for your support, highly appreciated. 

[Anonymous]

Hi @han-solo ,

There're several ways to do this:

1. You can manually partition your data into smaller chunks and load them incrementally. This involves:

• Creating multiple queries: Each query should load a subset of your data.
• Appending the data: After loading each subset, append it to your existing data model.

2.Dataflows in Power BI can help you manage large datasets by breaking them into more manageable pieces:

• Create a dataflow: Load your data into a dataflow in smaller chunks.
• Link the dataflow: Use the dataflow as a source in your Power BI model.

Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

3.Tabular Editor is a powerful tool for managing and optimizing your Power BI data models. It allows you to work with your models more efficiently, especially when dealing with large datasets. You can create partitions in your data model to handle different date ranges. This way, you can load and refresh data incrementally.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[powerbidev123]

HI @han-solo , you can implement incremental data loading. More information about this on https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Or you can use parameters or filters in Power Query to load one year's data at a time and then append each chunk manually or via Power Query append operations.

[han-solo]

Hi @powerbidev123 

Thank you very much for your quick reply - highly appreciated! 

incremental refresh currently is no option since the query is not SQL based and PowerBI cannot natively access the RangeStart and RangeEnd parameters. 

Therefore, the only option I'd currently see would be your alternative you mentioned. But as far as I understand, I would then end up with the same problem again if I appended chunk per chunk manually, published the dashboard and wanted to refresh it then again, no?

Thank you sir!