Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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_-1lZD3Yd-DYr4DJ2pGfuQ

(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-aHuCvYNpYBhwtEOFUZF6goQTbrEvW_5g

 

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

 

Need Help
  • slorin's avatar
    slorin
    2 years ago

    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

4 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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]

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      typo

      it should be

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