How to fetch data from middle of the another query

[B_kumar]

Hi

 i am new to the power query, i have a question,  can we fetch data from middle step of the another query ?

[ziying35]

@B_kumar 

All we need to do is convert three positions in the original code to comments using comment notation, and add a pair of parentheses.

I'll show you how to quickly convert code to record form with a simple query:

//query1
let
    a = 100,
    b = 45,
    c = a+b
in
    c

fisrt convert query1 to record form:

//query1
//let
    [
      a = 100,
      b = 45,
      c = a+b
    ]
//in
    //c

Code reference step from record form:

1. result of query1:

//result of query1
let
   out = query1[c]
in 
   out

or

//result of query1
let 
   out = Record.Field(query1, "c")
in
   out

2. Refer to the code above to reference any step of query1 in another query.

[ziying35]

Hi, @B_kumar 

If you want to reference data from an intermediate step of a query, that's fine, but for that you'll have to add a new query out.First write the query to which the intermediate data is to be referenced in record form.Its result needs to be referenced by another query as well.

[ImkeF · Accepted solution]

Hi @B_kumar ,

that's not possible.
You can only "reach" what you see: Either the end results of other queries or intermediate steps of the current query.

1. To reach your goal you have to divide the other query into to, so that the desired middle step will become the end result of the "staging" query.
2. To do this, check the step that follows the desired "middle" step.
3. Right-click mouse and select "Extract Previous".
4. You will be prompted with a dialogue to enter a name for the new query.
5. Click OK.
6. Select the newly created query -> right-click mouse and deselect "Enable load" to prevent this intermediate query being loaded to the data model

[Anonymous]

To add to @ImkeF response to exhaust all options :):

theoretically, you can return as many steps from the query as you want in the form of a list. Something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    InsertedAddition = Table.AddColumn(ChangedType, "Addition", each [Column1] + 1, type number)
in
    {Source, InsertedAddition}

This query can not return any data to visuals but is a valid query. In this case, you can refer to the Source and InsertedAddition steps independently (via list indexing) from outside of the query. However, if you thinking about using something like this, you probably need to really know what you do. In my opinion, this is quite a bad code and has a very limited application. I only used this once for evaluation tracing (due to how the evaluation model works you will not be able to use it for timing though).

In 100% the approach described by ImkeF provides a more structured and clean code.

Kind regards,

JB