Tuning Power query performance

Power Query
1

I have quite a large data set and some complex queries. About a week ago it all started to slowly unravel.

When looking in DAX studio the size of my data is 1GB (and it is increasing at 1% per day!!)
The data set is no longer refreshing it is timing out in the power bi service
The data sources are SQL / Power BI data flows / DAX

Is there a guide to follow to try and reduce the query time?
A list of best practices?
Any diagnostics?
The first query I am looking at tackling takes an hour + to get data from a dataflow

Thanks
E

2

Hi @ells,

Imke Feldmann has a collection of tips on how to improve performance of your M-queries.

1 Like
3

Thanks,
there is a couple of good options in there. Table.Buffer may help me as well as I have a couple of merges I use to restrict the fact tables.

Thanks
E

4

How complex is that merge?
Could you achieve the same creating filters based on List functions?

5

The Merge is on Geography
My Sales table has a country code
My Country table has a Country code

I only need the sales data if ‘Sales’[Country] exists in ‘Country’.[Country]

I also created a single table that just has the country codes I am interested in.

53 countries in all

Thanks
E

6

Hi @ells,

Okay see if this performs better than the Merge
In the Sales table query (in the advanced editor) create a new variable, something like:
ListCountries = List.Buffer(Country[Country])

Next create a filter step in Sales through the UI and change the M code in the formula bar to:
Table.SelectRows(#“You Prev Step Name”, each (List.Contains(ListCountries ,[Country])=true ))

I hope this is helpful.

7

Thanks this has been very succesfull when testing

is it possible to filte the list

ListCountries = List.Buffer(Country[Country] , [Country] = ‘GB’) ??

Many Thanks
E

8

Hi @ells,

Sure but if you only need to pass a limited number of fixed items why not create a list that only contains those values?

ListCountries = List.Buffer( List.Select( Country[Country] , each _ = “GB” ))

is equal to:

ListCountries = {“GB”}

9

@Melissa

With the selection what I am looking at is limiting my Buidget query to the last two years

in ‘Dim Calendar’ query I have a [Fiscal Year Offset] which is 0 for current year, -1 for previous year
in ‘Dim Calendar’ I have a column [skey_date] - surrogate key for the date
in ‘Fact Budget’ I have column [skey_date]

so what I am looking for i
ListDates - a list of the skey_date for cuurrent year and previous year
ListDate = List.Buffer( #‘Dim Calendar’[skey_date] )
However I need to add a filter to that so that it is only where [Fiscal Year Offset] is 0 or 1

Many Many Thanks
E

10

Hi @ells,

Alright. Thanks for that.
Paste this into a new blank query and give it a name like: ListDates

let
    Source = #"Dim Calendar"[[skey_date], [Fiscal Year Offset]],
    SelectDates = List.Buffer( Table.SelectRows(Source, each ([Fiscal Year Offset] = -1 or [Fiscal Year Offset] = 0))[skey_date] )
in
    SelectDates

Next for your Fact Budget table create this filter.

Table.SelectRows(#“You Prev Step Name”, each (List.Contains(ListDates ,[skey_date])=true ))

I hope this is helpful.

11

Awesome,
Thanks
E