Speed up Dataset Refresh

[EBGAL]

Hi, 

I have a dataset of 19M rows and 18 columns (50/50 between small text and numerical values).

I encountered an issue with refresh time after applying below modification:
......
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Group] = "VALUE") and ([ID] = 1 or [ID] = 2 or [ID] = 3 or [ID] = 4 or [ID] = 5)),
#"Indices" = #"Filtered Rows"[Index],
#"Excluded Patent GLs" = Table.SelectRows(#"Added Index", each not List.Contains(List.Buffer(#"Indices"), [Index]))

It takes 11 mins to refresh without the modifcation and hours with. As long as this way of doing it creates a throttle, I am looking for a better way to do the manipulation. 

Data comes from an Azure SQL database.

Thanks

Evan

[themistoklis · Accepted solution]

@EBGAL 

Is this the only manipulation that you do on the dataset?

Maybe it is best if you let the Azure database do some of the transformations (considering the high number of records 19 mil.) before you insert the dataset to PowerBI, by creating a VIEW.

[EBGAL]

Hi @themistoklis ,

SQL transformation is one of the options I considered but this involves other team's time that they currently lack.

There is another transformation that occurs in the dataset, however, the refresh time does not get significantly affected if I cut it off. I narrowed down to the aforementioned transformation by adding/deleting steps in Power Query and checking the refresh time.

Thanks

Evan