Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
(watch the "parcels-query" when doing a "refresh all"
The excel file can be found on
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
Solved! Go to 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
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]
typo
it should be
ListBuffer = List.Buffer(#"DATADEF-PAND-query"),
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