none
Reference Different than The Last Step of Query RRS feed

  • Question

  • Hi guys,

    situation:

    I have a query with 50 steps. Say I want to create a new query via "Reference" feature but I dont want to reference the last step of the original 50 step query. I want to reference e.g. step 46 which is called #"Added Column". 

    Is this possible?

    DZ

    Monday, August 8, 2016 9:24 AM

Answers

  • Guys,
    I appreciate the suggestions but these solutions are a bit cumbersome.

    I was looking for a sleaker solution, where I just setup everything once without necessity to change the code all the time when I want to see my customized "preview".

    I guess it is not really possible to do what I am looking for.

    Anyway, thx for help!

    DZ

    • Proposed as answer by Nin Sute Wednesday, August 10, 2016 12:27 PM
    • Marked as answer by Daniel Zrust Wednesday, August 10, 2016 12:28 PM
    Wednesday, August 10, 2016 7:41 AM

All replies

  • I'm assuming: 1.  that you are using reference to preserve a single copy of the standard code applicable to both (or more) queries else you can use copy; 2. There is a condition that would cause you to display different results.  Then a simple if condition might suffice.  Consider the following.  Many thanks.

    let
        Source = { 1, 2, 3, 4, 5, 99 },
        Max = List.Max( Source ),
        Avg = List.Average( Source ),
        DayOfWeek = Date.DayOfWeek (Date.From(DateTime.LocalNow() ) ),
    //End of standard code; start of custom code for each "referenced" query
    //Different results for different states
        Answer = if (DayOfWeek = 0) then Max else Avg
    in 
        Answer


    Monday, August 8, 2016 2:02 PM
  • 1. Correct. I dont want to copy.

    2. Not really.

    My use case is that:

    I do a lot of transformations in a text file. Let's call it query 1 with 50 steps. Just bunch of rows with 10 columns. Then, I need to have some sort of preview how the query 1 looks after specific step within a separate query (Query 2). I want to keep working on query 1 but at the same time, I want to see how the query 1 looks e.g. after the 46th step. And I want to see this state in query 2.

    Does it make sense?

    DZ 

    Tuesday, August 9, 2016 8:44 AM
  • HI Dan,

    Since PQ offers robust preview capability for each step of any query, I don't see why the second query is needed. Why is the builtin preview capability insufficient?  Many thanks.

    Tuesday, August 9, 2016 9:41 AM
  • Well, sometimes my queries are so complicated that I want to have an anchor point I can always look at with various filters applied so I can check everything is still ok while not having the filters applied in the original query.

    Or sometimes I am building query 2 related to query 1 but not to the last step of the query 1. So it would be really cool to be able to reference specific steps within a query, not just the last one.

    Trust me, this feature would be really useful.

    DZ

    Tuesday, August 9, 2016 11:37 AM
  • But that's the point I guess.  You can select any step of any query and PQ will preview the query up to and including that step.  I see a lot of 1. develop query 1 (to a point), 2. Create referenced query 2 as anchor 3. test query 2 4. change query 1 per results of query 2 testing.  Many thanks
    Tuesday, August 9, 2016 1:26 PM
  • Well, thing is that I dont want really do 4th step.

    I just want to have some filters applied to query 1 via query 2 at all times. Otherwise, you have add/remove filters all the time in the query 1...

    Also frequently, I check be deduplicating various columns and I would also be nice to have to have this ready, e.g. in a query 3.


    Tuesday, August 9, 2016 3:44 PM
  • Hi DZ. You can do what you're wanting using a variation of what Nin suggested above. Just add a "fake" final step to Query1 that normally references the "real" final step. When you want to see what Query2 looks like if you use step 46 as the final step in Query1, just change the fake final step to point to step 46.

    Does that provide what you're looking for?

    Ehren

    Tuesday, August 9, 2016 5:21 PM
    Owner
  • To elaborate on what Ehren says (or my interpretation of this), when you look at a script in Power Query you'll see something like

    let

      B = <some function on A>,

      C = <some function on B>,

      D = <some function on C>

    in

      D

    You can change the final step to reference an earlier variable than the last, for instance

    let

      B = <some function on A>,

      C = <some function on B>,

      D = <some function on C>

    in

      C

    and the query will return C instead of D.

    Wednesday, August 10, 2016 7:34 AM
  • Guys,
    I appreciate the suggestions but these solutions are a bit cumbersome.

    I was looking for a sleaker solution, where I just setup everything once without necessity to change the code all the time when I want to see my customized "preview".

    I guess it is not really possible to do what I am looking for.

    Anyway, thx for help!

    DZ

    • Proposed as answer by Nin Sute Wednesday, August 10, 2016 12:27 PM
    • Marked as answer by Daniel Zrust Wednesday, August 10, 2016 12:28 PM
    Wednesday, August 10, 2016 7:41 AM