Using variable vs. referencing another query

[frithjof_v]

Hi,

I am wondering if there is some obvious conceptual difference (which may impact performance) between referencing a variable (i.e. referencing another step in the same query), or referencing the output of another query, when creating a custom column?

An example:

Let's say I have a very simple table, it's just a list of numbers from 1 to 10 000 000.

I want to add a Timestamp column to this table.

Does it make a difference if I reference a variable (step) of the same query:

let
    Source = List.Numbers(1, 10000000),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    NowVariable = DateTimeZone.UtcNow(),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Timestamp", each NowVariable)
in
    #"Added Custom"

Or if I reference another query:

let
    Source = List.Numbers(1, 10000000),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Timestamp", each NowQuery)
in
    #"Added Custom"

The referenced query, NowQuery, is created as simple as this:

let
    Source = DateTimeZone.UtcNow()
in
    Source

This is an example, however I am also generally interested in understanding if there is a conceptual difference (performance-wise) between using a variable (a step inside the same query) or using the output of another query? 

[tharunkumarRTK]

@frithjof_v 
In terms of performance, I think referencing the variable step in the same query would be a better option. Because, each individual query evaulates independently. You can find more information in chirs webb's blog 
https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[frithjof_v]

Thanks @tharunkumarRTK,

I was thinking something similar.

However I tried refreshing the two tables separately in Power BI Desktop, and by looking in SQL Server Profiler it seems the Command End events for both tables had very similar CPUTime and Duration. 

(I'm not sure if the Command Begin and Command End are the most relevant events to track. If anyone have suggestion about which events I should track, please let me know.)