Forum Discussion
Using variable vs. referencing another query - Excel source
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.
Thank you PwerQueryKees ,
It also seems to me that it doesn't make a difference (or maybe, see * below) if we reference another query, or reference a step (variable) inside the same query.
(If this is not true, I hope someone will correct me! 😀)
I did some testing, with inspiration from the example shown in this video: Inside Power Query reference queries for Power BI and Excel (youtube.com)
I created a Power Automate flow which gets triggered by someone making a HTTP GET call, and the Power Automate flow returns a JSON with some dummy data to the caller:
I then used Power BI desktop (and later also Power BI service) to make HTTP calls to this URL endpoint. I tried some different query configurations in Power Query.
1. Merging multiple queries (not loaded) into a main query (loaded).
The main query (which gets loaded into the semantic model) has this M code:
let
Source = Json.Document(Web.Contents("<URL Endpoint>")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded transactions" = Table.ExpandListColumn(#"Converted to Table", "transactions"),
#"Expanded transactions1" = Table.ExpandRecordColumn(#"Expanded transactions", "transactions", {"transaction_id", "customer_id", "order_date", "items", "total_amount"}, {"transactions.transaction_id", "transactions.customer_id", "transactions.order_date", "transactions.items", "transactions.total_amount"}),
#"Expanded transactions.items" = Table.ExpandListColumn(#"Expanded transactions1", "transactions.items"),
#"Expanded transactions.items1" = Table.ExpandRecordColumn(#"Expanded transactions.items", "transactions.items", {"item_id", "description", "quantity", "price_per_unit"}, {"transactions.items.item_id", "transactions.items.description", "transactions.items.quantity", "transactions.items.price_per_unit"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded transactions.items1",{{"transactions.transaction_id", type text}, {"transactions.customer_id", type text}, {"transactions.order_date", type date}, {"transactions.items.item_id", type text}, {"transactions.items.description", type text}, {"transactions.items.quantity", Int64.Type}, {"transactions.items.price_per_unit", Int64.Type}, {"transactions.total_amount", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"transactions.transaction_id", "transactions.items.item_id"}, #"APISource QueryA_1", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult", JoinKind.LeftOuter),
#"Expanded MergeResult" = Table.ExpandTableColumn(#"Merged Queries", "MergeResult", {"transactions.total_amount"}, {"B.transactions.total_amount"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded MergeResult", {"transactions.transaction_id", "transactions.items.item_id"}, #"APISource QueryA_2", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult2", JoinKind.LeftOuter),
#"Expanded MergeResult2" = Table.ExpandTableColumn(#"Merged Queries2", "MergeResult2", {"transactions.total_amount"}, {"B.transactions.total_amount2"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded MergeResult2", {"transactions.transaction_id", "transactions.items.item_id"}, #"APISource QueryA_3", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult3", JoinKind.LeftOuter),
#"Expanded MergeResult3" = Table.ExpandTableColumn(#"Merged Queries3", "MergeResult3", {"transactions.total_amount"}, {"B.transactions.total_amount3"}),
#"Merged Queries4" = Table.NestedJoin(#"Expanded MergeResult3", {"transactions.transaction_id", "transactions.items.item_id"}, #"APISource QueryA_4", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult4", JoinKind.LeftOuter),
#"Expanded MergeResult4" = Table.ExpandTableColumn(#"Merged Queries4", "MergeResult4", {"transactions.total_amount"}, {"B.transactions.total_amount4"})
in
#"Expanded MergeResult4"
Each of the queries A_1, A_2, A_3, A_4 (which don't get loaded to the semantic model, but gets merged into the main query) basically just call the API (same URL Endpoint as in the main query):
let
Source = Json.Document(Web.Contents("<URL Endpoint>")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded transactions" = Table.ExpandListColumn(#"Converted to Table", "transactions"),
#"Expanded transactions1" = Table.ExpandRecordColumn(#"Expanded transactions", "transactions", {"transaction_id", "customer_id", "order_date", "items", "total_amount"}, {"transactions.transaction_id", "transactions.customer_id", "transactions.order_date", "transactions.items", "transactions.total_amount"}),
#"Expanded transactions.items" = Table.ExpandListColumn(#"Expanded transactions1", "transactions.items"),
#"Expanded transactions.items1" = Table.ExpandRecordColumn(#"Expanded transactions.items", "transactions.items", {"item_id", "description", "quantity", "price_per_unit"}, {"transactions.items.item_id", "transactions.items.description", "transactions.items.quantity", "transactions.items.price_per_unit"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded transactions.items1",{{"transactions.transaction_id", type text}, {"transactions.customer_id", type text}, {"transactions.order_date", type date}, {"transactions.items.item_id", type text}, {"transactions.items.description", type text}, {"transactions.items.quantity", Int64.Type}, {"transactions.items.price_per_unit", Int64.Type}, {"transactions.total_amount", Int64.Type}})
in
#"Changed Type"
Refreshing that PBIX file results in 1 run* of the Power Automate flow.
So it seems the Power Query engine evaluates the main (loaded) query and all the referenced queries, and determines that the source is the same, and therefore can call the source once and then cache the source data. So this (referencing other queries) seems to be just as performant as referencing an existing variable inside the main query.
*Sometimes 2 runs actually (see the Refresh PBIX on the bottom).
I don't know why, maybe one of the runs is some "test connection" which Power Query makes.
Perhaps this is a difference between this option 1. and another option 3. (see below) which references a variable inside the main query.
However I need to try to do more runs to see if also option 3. will produce 2 runs sometimes.
2. Loading multiple queries.
The only difference from option 1. is that in option 2. I have also enabled load on all the queries.
Now, because I have enabled load on all the queries, one refresh of this PBIX file results in multiple runs of the Power Automate flow. I think it is because Power Query cannot share cached results between different loaded queries.
3. Merging with a variable defined inside the main query.
Here, I only have one query, and I am merging a variable inside the query (which calls the API) multiple times into the "main branch" of the query.
let
Source = Json.Document(Web.Contents("<URL Endpoint>")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded transactions" = Table.ExpandListColumn(#"Converted to Table", "transactions"),
#"Expanded transactions1" = Table.ExpandRecordColumn(#"Expanded transactions", "transactions", {"transaction_id", "customer_id", "order_date", "items", "total_amount"}, {"transactions.transaction_id", "transactions.customer_id", "transactions.order_date", "transactions.items", "transactions.total_amount"}),
#"Expanded transactions.items" = Table.ExpandListColumn(#"Expanded transactions1", "transactions.items"),
#"Expanded transactions.items1" = Table.ExpandRecordColumn(#"Expanded transactions.items", "transactions.items", {"item_id", "description", "quantity", "price_per_unit"}, {"transactions.items.item_id", "transactions.items.description", "transactions.items.quantity", "transactions.items.price_per_unit"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded transactions.items1",{{"transactions.transaction_id", type text}, {"transactions.customer_id", type text}, {"transactions.order_date", type date}, {"transactions.items.item_id", type text}, {"transactions.items.description", type text}, {"transactions.items.quantity", Int64.Type}, {"transactions.items.price_per_unit", Int64.Type}, {"transactions.total_amount", Int64.Type}}),
Source_B = Json.Document(Web.Contents("<URL Endpoint>")),
#"Converted to Table_B" = Table.FromRecords({Source_B}),
#"Expanded transactions_B" = Table.ExpandListColumn(#"Converted to Table_B", "transactions"),
#"Expanded transactions1_B" = Table.ExpandRecordColumn(#"Expanded transactions_B", "transactions", {"transaction_id", "customer_id", "order_date", "items", "total_amount"}, {"transactions.transaction_id", "transactions.customer_id", "transactions.order_date", "transactions.items", "transactions.total_amount"}),
#"Expanded transactions.items_B" = Table.ExpandListColumn(#"Expanded transactions1_B", "transactions.items"),
#"Expanded transactions.items1_B" = Table.ExpandRecordColumn(#"Expanded transactions.items_B", "transactions.items", {"item_id", "description", "quantity", "price_per_unit"}, {"transactions.items.item_id", "transactions.items.description", "transactions.items.quantity", "transactions.items.price_per_unit"}),
#"Changed Type_B" = Table.TransformColumnTypes(#"Expanded transactions.items1_B",{{"transactions.transaction_id", type text}, {"transactions.customer_id", type text}, {"transactions.order_date", type date}, {"transactions.items.item_id", type text}, {"transactions.items.description", type text}, {"transactions.items.quantity", Int64.Type}, {"transactions.items.price_per_unit", Int64.Type}, {"transactions.total_amount", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"transactions.transaction_id", "transactions.items.item_id"}, #"Changed Type_B", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult", JoinKind.LeftOuter),
#"Expanded MergeResult" = Table.ExpandTableColumn(#"Merged Queries", "MergeResult", {"transactions.total_amount"}, {"B.transactions.total_amount"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded MergeResult", {"transactions.transaction_id", "transactions.items.item_id"}, #"Changed Type_B", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult2", JoinKind.LeftOuter),
#"Expanded MergeResult2" = Table.ExpandTableColumn(#"Merged Queries2", "MergeResult2", {"transactions.total_amount"}, {"B.transactions.total_amount2"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded MergeResult2", {"transactions.transaction_id", "transactions.items.item_id"}, #"Changed Type_B", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult3", JoinKind.LeftOuter),
#"Expanded MergeResult3" = Table.ExpandTableColumn(#"Merged Queries3", "MergeResult3", {"transactions.total_amount"}, {"B.transactions.total_amount3"}),
#"Merged Queries4" = Table.NestedJoin(#"Expanded MergeResult3", {"transactions.transaction_id", "transactions.items.item_id"}, #"Changed Type_B", {"transactions.transaction_id", "transactions.items.item_id"}, "MergeResult4", JoinKind.LeftOuter),
#"Expanded MergeResult4" = Table.ExpandTableColumn(#"Merged Queries4", "MergeResult4", {"transactions.total_amount"}, {"B.transactions.total_amount4"})
in
#"Expanded MergeResult4"
This also results in only 1 run of the Power Automate flow each time I refresh the PBIX.
* However, as mentioned above, I need to test more to see if this sometimes results in 2 runs of the Power Automate flow, as option 1. does.
Just for reference, the JSON returned by the API is just some dummy data which ChatGPT generated for me for the sake of this experiment:
{ "transactions": [ { "transaction_id": "T1001", "customer_id": "C1001", "order_date": "2024-07-01", "items": [ { "item_id": "I1001", "description": "Laptop", "quantity": 1, "price_per_unit": 1200 }, { "item_id": "I1002", "description": "Mouse", "quantity": 2, "price_per_unit": 25 } ], "total_amount": 1250 }, { "transaction_id": "T1002", "customer_id": "C1002", "order_date": "2024-07-03", "items": [ { "item_id": "I1003", "description": "Smartphone", "quantity": 1, "price_per_unit": 800 }, { "item_id": "I1004", "description": "Earphones", "quantity": 1, "price_per_unit": 50 } ], "total_amount": 850 }, { "transaction_id": "T1003", "customer_id": "C1001", "order_date": "2024-07-05", "items": [ { "item_id": "I1005", "description": "Tablet", "quantity": 1, "price_per_unit": 600 } ], "total_amount": 600 }, { "transaction_id": "T1004", "customer_id": "C1003", "order_date": "2024-07-07", "items": [ { "item_id": "I1006", "description": "Monitor", "quantity": 2, "price_per_unit": 200 } ], "total_amount": 400 }, { "transaction_id": "T1005", "customer_id": "C1004", "order_date": "2024-07-08", "items": [ { "item_id": "I1007", "description": "Keyboard", "quantity": 1, "price_per_unit": 100 }, { "item_id": "I1002", "description": "Mouse", "quantity": 1, "price_per_unit": 25 } ], "total_amount": 125 }, { "transaction_id": "T1006", "customer_id": "C1005", "order_date": "2024-07-09", "items": [ { "item_id": "I1008", "description": "Headphones", "quantity": 1, "price_per_unit": 150 } ], "total_amount": 150 }, { "transaction_id": "T1007", "customer_id": "C1006", "order_date": "2024-07-10", "items": [ { "item_id": "I1009", "description": "Printer", "quantity": 1, "price_per_unit": 300 } ], "total_amount": 300 }, { "transaction_id": "T1008", "customer_id": "C1007", "order_date": "2024-07-11", "items": [ { "item_id": "I1010", "description": "Webcam", "quantity": 1, "price_per_unit": 80 } ], "total_amount": 80 }, { "transaction_id": "T1009", "customer_id": "C1008", "order_date": "2024-07-12", "items": [ { "item_id": "I1011", "description": "External Hard Drive", "quantity": 1, "price_per_unit": 120 } ], "total_amount": 120 }, { "transaction_id": "T1010", "customer_id": "C1009", "order_date": "2024-07-13", "items": [ { "item_id": "I1012", "description": "USB-C Hub", "quantity": 1, "price_per_unit": 50 }, { "item_id": "I1013", "description": "Charging Cable", "quantity": 2, "price_per_unit": 10 } ], "total_amount": 70 }, { "transaction_id": "T1011", "customer_id": "C1010", "order_date": "2024-07-14", "items": [ { "item_id": "I1014", "description": "Wireless Charger", "quantity": 1, "price_per_unit": 40 } ], "total_amount": 40 }, { "transaction_id": "T1012", "customer_id": "C1011", "order_date": "2024-07-15", "items": [ { "item_id": "I1015", "description": "Bluetooth Speaker", "quantity": 1, "price_per_unit": 60 } ], "total_amount": 60 }, { "transaction_id": "T1013", "customer_id": "C1012", "order_date": "2024-07-16", "items": [ { "item_id": "I1016", "description": "Smartwatch", "quantity": 1, "price_per_unit": 200 } ], "total_amount": 200 }, { "transaction_id": "T1014", "customer_id": "C1013", "order_date": "2024-07-17", "items": [ { "item_id": "I1017", "description": "Fitness Tracker", "quantity": 1, "price_per_unit": 100 } ], "total_amount": 100 }, { "transaction_id": "T1015", "customer_id": "C1014", "order_date": "2024-07-18", "items": [ { "item_id": "I1018", "description": "VR Headset", "quantity": 1, "price_per_unit": 400 } ], "total_amount": 400 }, { "transaction_id": "T1016", "customer_id": "C1015", "order_date": "2024-07-19", "items": [ { "item_id": "I1019", "description": "Drone", "quantity": 1, "price_per_unit": 500 } ], "total_amount": 500 }, { "transaction_id": "T1017", "customer_id": "C1016", "order_date": "2024-07-20", "items": [ { "item_id": "I1020", "description": "Gaming Console", "quantity": 1, "price_per_unit": 450 } ], "total_amount": 450 }, { "transaction_id": "T1018", "customer_id": "C1017", "order_date": "2024-07-21", "items": [ { "item_id": "I1021", "description": "Action Camera", "quantity": 1, "price_per_unit": 300 } ], "total_amount": 300 }, { "transaction_id": "T1019", "customer_id": "C1018", "order_date": "2024-07-22", "items": [ { "item_id": "I1022", "description": "E-Reader", "quantity": 1, "price_per_unit": 120 } ], "total_amount": 120 } ] }Nice testing! Thanks for the new insights...