The PQ Editor only recognizes steps if the "in" expression is a simple, unmodified reference to the last let variable. Otherwise, the entire query expression, even if it's a let expression, is treated as a generic chunk of M.
What about the "let" expression causes the Applied Steps to disappear?
Consider the following query:
let a = 1, b = 2
in a
The identifier b
is never referenced. From my (admittedly very limited) knowledge of the engine behind M, unreferenced identifiers are ignored by the engine. When this expression is written into a query, the Applied Steps panel in the Power Query Editor interface within Excel clears itself of all steps and replaces them with a single step with the same name as the query. This happens usually when the last identifier goes unreferenced. The expression resolves to the correct value, but the Applied Steps are gone. Having an unreferenced identifier is likely an error, but during testing it can be beneficial.
I have attempted writing a number of different let
expressions and I haven't found a common thread between the expressions that cause this "error"—or whatever it may be. Look at the following expressions for my findings. Is this a bug in the Power Query software? I can find no rhyme or reason to this behavior.
let a = 1, b = 2
in b // No error, first identifier unreferenced
let a = 1, b = 2
in a // Steps missing, last identifier unreferenced
let a = b, b = 2
in b // No error, first identifier unreferenced but referenced identifier used in unreferenced expression
let a = b, b = 2
in a // Steps missing, last identifier referenced indirectly after the "in"
let a = 2, b = a, c = 4
in a + c // Steps missing, middle identifier unreferenced
let a = 2, b = a, c = 4
in c // No error, first and middle identifier unreferenced
let a = 2, b = a, c = 4
in a + b + c // Steps missing, no identifiers unreferenced!
1 answer
Sort by: Most helpful
-
Ehren (MSFT) 1,781 Reputation points Microsoft Employee
2021-07-26T22:59:46.323+00:00