Dataflows with historical or recent data

[Anonymous]

Hi

I'm starting to use PowerBi Dataflow. I've created a dataflow with historical data from customers. It has about 50 millions rows..

In this historical data, I also have the updated data, by using a date field.

I want to create two types of PBI Reports:

a) Reports that use the historical data from previous years to curent

b) Reports that only use date from current year.

The trouble is that when I load the data into PowerBi desktop, it always loads the whole dataflow, making it very slow and loading about 5 GB of data..

Is there any way to have a single dataflow, but allowing me to get only the last year from this dataflow?

Or I should have two dataflows, one historical, and one "last year" ?

This is an example... I'm loading 3 rows from a dataflow, but it load 1,8 Gb.. and of couse, this takes a lot of time.

Thanks!

[v-shex-msft]

HI @Anonymous,

I'd like to suggest you create sub queries to reference raw dataflow entities and adding filters based on 'DateTime.LocalNow' function and extract the year part for calculating. You can get data from these subquery tables to loading records in a specific date range.

Filter by Today’s date in Power Query 

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks

I tried to do something similar, by filtering by a fixed value for a column in PowerBi desktop. Should it be the same? (Except, of course, a dinamic range of dates)

I ask, because with this filter in PowerBI desktop, I still have to download the whole dataset, and just then, it filters the data.

Thanks!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

In fact, these steps are operated on power bi service dataflow side.

You can export the 'JSON' file with existed dataflow, then you can import it to quickly create a duplicate of specific dataflow. You can edit this entity and add a query filter into the connector.
For example,  you are using SQL connector then raw dataflow not add any query, you can add filter queries to the duplicated entity to reduce data amount.
After these steps, you can use power bi dataflow connect to get data from the duplicated dataflow instead of getting data from raw dataflow with huge amount records.

BTW, the above steps are used to prevent to use 'computed entity' feature which required a premium license.
Regards,

Xiaoxin Sheng

[Anonymous]

That is useful.

I have premium license, so I will try with computer entity.

Altought I was expecting the dataflow to act like "dataset", where I connect the dataset, but I don't have the need to download all the data.

Thanks!