Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
bartplessers
Regular Visitor

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...

(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

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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

View solution in original post

4 REPLIES 4
bartplessers
Regular Visitor

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]

 

2023-12-20_10-04-10.png

 

typo

it should be

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

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

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

 

 

 

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors