Performance issue: query much slower when using the result of another query as parameter

[bartplessers]

Hi,

I noticed some weird performance issues when using a query as parameter.

I have an Excel with

- tab "Data", containing some sample data

- tab "DATADEF", containing a matrix with some definitions

Now, I have a powerquery that merges 2 columns.

I have done this on 3 ways

1. parcels-manual

just by using the GUI, combining 2 columns "Capakey" and "Partnumber": 

let
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Capakey", "Partnumber"},Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"PAND")
in
    #"Merged Columns"

2. parcels-fixed

First, I defined a list "DATADEF-PAND-fixed"

let
    #"Result" = {"Capakey", "Partnumber"}
in
    #"Result"

that I use in my query "parcels-fixed"

let
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,#"DATADEF-PAND-fixed",Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"PAND")
in
    #"Merged Columns"

3. parcels-query

I want to have things more dynamically, so I made a query "DATADEF-PAND-query" that generates the list of columns that should be combined:

let
    #"Source" = DATADEF,
    #"Filtered Rows" = Table.SelectRows(Source, each [PAND] <> null and [PAND] <> ""),
    #"Result" = #"Filtered Rows"[NAME]
in
    #"Result"

and again, I use this list as parameter in "parcels-query"

let
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,#"DATADEF-PAND-query",Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"PAND")
in
    #"Merged Columns"

Now I noticed that when I refresh all my data, the queries "parcels-manual" and "parcels-fixed" are fast, but the "parcels-query" is really slow.

I made a video of this, you can see this clearly on

https://kuleuven-my.sharepoint.com/:v:/g/personal/bart_plessers_kuleuven_be/EXSkRG29q5RBiUjFZO0-k00B...

(watch the "parcels-query" when doing a "refresh all"

The excel file can be found on 

https://kuleuven-my.sharepoint.com/:x:/g/personal/bart_plessers_kuleuven_be/EYdo7oBcNMdPl-aHuCvYNpYB...

What am I doing wrong? 

How can I use the result of a query as input for another function without this performance issue?

kind regards,

Bart Plessers

[bartplessers]

Hi @slorin ,

Unfortunatly this gives an error

Expression.Error: We cannot convert the value "DATADEF-PAND-query" to type List.
Details:
    Value=DATADEF-PAND-query
    Type=[Type]

[bartplessers]

typo

it should be

ListBuffer = List.Buffer(#"DATADEF-PAND-query"),

[slorin · Accepted solution]

Hi

Try with List.Buffer 

let
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
ListBuffer = List.Buffer("DATADEF-PAND-query"),
    #"Merged Columns" = Table.CombineColumns(Source,ListBuffer,Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"PAND")
in
    #"Merged Columns

Stéphane

[bartplessers]

Hi @slorin 

Your solution works really well!

My powerqeury became so fast that I even have some time left to ask some other stupid questions... 🙂

What I really don't understand is the difference in performance between

1. parcels-manual

just by using the GUI, combining 2 columns "Capakey" and "Partnumber": 

let
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Capakey", "Partnumber"},Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"PAND")
in
    #"Merged Columns"

2. parcels-fixed

First, I defined a list "DATADEF-PAND-fixed"

let
    #"Result" = {"Capakey", "Partnumber"}
in
    #"Result"

that I use in my query "parcels-fixed"

let
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,#"DATADEF-PAND-fixed",Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"PAND")
in
    #"Merged Columns"

Do I need to conclude that calling any function (i.e. "#DATADEF-PAND-fixed") always causes a delay, even when this function doesn't do anything?

And I this function in the above example excecuted for every record in my dataset? Or why does it cause so much delay?

Is there any drawback on buffering? Or should I always buffer lists? And if so, why is this not the standard behaviour?

The documentation is very sparse on this
List.Buffer - PowerQuery M | Microsoft Learn

The documentation for Table.buffer is a little bit more elaborated

Table.Buffer - PowerQuery M | Microsoft Learn

Here I can read

In some cases, it can make your queries run more slowly due to the added cost of reading all the data and storing it in memory

So I'm a bit confused when and when not to use the buffer....

kind regards

bartplessers