Using variable vs. referencing another query - Excel source

Question

Hi,&nbsp;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 doing a merge operation which doesn't fold?&nbsp;An example:&nbsp;Let's say I have two Excel sources.Both Excel sources are very simple tables, with an ID column with increasing values from 1 to 1 000 000 and also a column containing random values.&nbsp;Excel A&nbsp;Excel B&nbsp;In Power Query, I want to merge these sources by using the ID column.&nbsp;Does it make a difference if I reference a variable (step) of the same query:&nbsp;&nbsp;let
    Source = Excel.Workbook(File.Contents("...\Excel A.xlsx"), null, true),
    Ark1_Sheet = Source{[Item="Ark1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Ark1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Random", Int64.Type}}),
    Source_B = Excel.Workbook(File.Contents("...\Excel B.xlsx"), null, true),
    Ark1_Sheet_B = Source_B{[Item="Ark1",Kind="Sheet"]}[Data],
    #"Promoted Headers_B" = Table.PromoteHeaders(Ark1_Sheet_B, [PromoteAllScalars=true]),
    #"Changed Type_B" = Table.TransformColumnTypes(#"Promoted Headers_B",{{"ID", Int64.Type}, {"Random", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Changed Type_B", {"ID"}, "MergeResult", JoinKind.LeftOuter),
    #"Expanded MergeResult" = Table.ExpandTableColumn(#"Merged Queries", "MergeResult", {"Random"}, {"B.Random"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded MergeResult", {"ID"}, #"Changed Type_B", {"ID"}, "MergeResult2", JoinKind.LeftOuter),
    #"Expanded MergeResult2" = Table.ExpandTableColumn(#"Merged Queries2", "MergeResult2", {"Random"}, {"B.Random2"})
in
    #"Expanded MergeResult2"&nbsp;&nbsp;&nbsp;&nbsp;Or if I reference another query:&nbsp;&nbsp;let
    Source = Excel.Workbook(File.Contents("...\Excel A.xlsx"), null, true),
    Ark1_Sheet = Source{[Item="Ark1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Ark1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Random", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"ExcelSource QueryB", {"ID"}, "ExcelSource B", JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"ExcelSource B", "MergeResult"}}),
    #"Expanded MergeResult" = Table.ExpandTableColumn(#"Renamed Columns", "MergeResult", {"Random"}, {"B.Random"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded MergeResult", {"ID"}, #"ExcelSource QueryB", {"ID"}, "MergeResult2", JoinKind.LeftOuter),
    #"Expanded MergeResult2" = Table.ExpandTableColumn(#"Merged Queries1", "MergeResult2", {"Random"}, {"MergeResult2.Random"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded MergeResult2",{{"MergeResult2.Random", "B.Random2"}})
in
    #"Renamed Columns1"&nbsp;&nbsp;&nbsp;The referenced query, ExcelSource QueryB, is created as simple as this:&nbsp;&nbsp;let
    Source = Excel.Workbook(File.Contents("...\Excel B.xlsx"), null, true),
    Ark1_Sheet = Source{[Item="Ark1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Ark1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Random", Int64.Type}})
in
    #"Changed Type"&nbsp;&nbsp;&nbsp;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?&nbsp;&nbsp;E.g. if I wanted to append the two Excel sources.&nbsp;Also if I wanted to reuse source B multiple times within query A.Is there a conceptual difference (performance-wise) between using a variable from within the same query, or referencing another query?&nbsp;&nbsp;&nbsp;By connecting SQL Server Profiler to Power BI Desktop, and refreshing each of the two queries, I cannot see a noticeable difference between the two approaches:I haven't tested this with Power BI Service (powerbi.com), only with Power BI Desktop.&nbsp;My foundational question is, are there performance benefits of using variables in Power Query (M)?

pwerquerykees · Answer

Ok. Potatoe potatoe i guess. The only point I was trying to make is that the variables only get a value at the moment you reference them. Unlike in mist other popular languages where the assignment is executed as statements in the program flow.

This concept is also key in understanding M execution flow. It has also performance consequences because M will to a large extent only compute what is actually needed, potentially retrieving a lot less data than you would think if you regard the various variable assignments as sequential steps.

pwerquerykees · Answer

And I looked it up. The language reference calls them variables.I stand corrected... 😬

pwerquerykees · Answer

And I almost forgot. I did a bit of testing today. Calling a function defined as a variable in the same query or as a separate independent query takes so little time, I couldn't measure either by taking timestamps before and after the evaluation.

I could measure a 80 ms function, but had to do a sum of a 1 million element list to get to that time range and I could not distinguish the execution times of the internal from the external call.

To test it properly I think I would need to do a list.acxumulate of a million items list to find the difference if there is any.

My guess is that it does not make a difference, because once the function is compiled and in memory, they would be indistinguishable. And the compiling happens only once, so would not be noticeable anyway.

pwerquerykees · Answer

I don't know if there is a performance difference. I never noticed it, but that doesn' t mean anything.

BUT: Talking about concepts: What you call variables, really aren't! Conceptually they are named references to another formula.

M is a functional programming language. It starts at the end en then evaluates all names it encounters as needed. Implying that names not needed, are never evaluated!

frithjof_v · Answer

Thank you&nbsp;PwerQueryKees!&nbsp;By reading these pages, I get the impression that each step ("named identifier") in the M code is a separate variable:&nbsp;M Language basic concepts - PowerQuery M | Microsoft LearnPower Query M Primer (Part 4): Variables &amp; Identifiers | Ben Gribaudo&nbsp;So when I was referring to variables, I simply mean a step ("named identifier") in the M code.&nbsp;Isn't that correct?&nbsp;I'm interested to learn more about the nuances here&nbsp;😀I'm struggling to see the difference between a variable and a step in the M code.&nbsp;Isn't each step in the M code a variable?&nbsp;(And I guess each variable can only be accessed locally inside the M query where the variable (i.e. step) was created?Meaning, I cannot access a variable (step) created inside one M query from another M query?).&nbsp;Thanks!

Forum Discussion

Using variable vs. referencing another query - Excel source

10 Replies

Related content

Query referencing another query

Referencing table as a variable

Referencing notebook exit value as a variable in a data pipeline

Referencing one Table with Another

Referencing a Survey from Column names as Variables