Forum Discussion
How to fetch data from middle of the another query
Hi
i am new to the power query, i have a question, can we fetch data from middle step of the another query ?
Hi Anonymous ,
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.- 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.
- To do this, check the step that follows the desired "middle" step.
- Right-click mouse and select "Extract Previous".
- You will be prompted with a dialogue to enter a name for the new query.
- Click OK.
- Select the newly created query -> right-click mouse and deselect "Enable load" to prevent this intermediate query being loaded to the data model
4 Replies
- ImkeF
Community Champion
Hi Anonymous ,
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.- 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.
- To do this, check the step that follows the desired "middle" step.
- Right-click mouse and select "Extract Previous".
- You will be prompted with a dialogue to enter a name for the new query.
- Click OK.
- Select the newly created query -> right-click mouse and deselect "Enable load" to prevent this intermediate query being loaded to the data model
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
- ziying35
Impactful Individual
Hi, Anonymous
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.
- ziying35
Anonymous
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 cfisrt convert query1 to record form:
//query1 //let [ a = 100, b = 45, c = a+b ] //in //cCode reference step from record form:
1. result of query1:
//result of query1 let out = query1[c] in outor
//result of query1 let out = Record.Field(query1, "c") in out2. Refer to the code above to reference any step of query1 in another query.
