Results 1 to 2 of 2

Thread: Using Query results in subsequent queries

  1. #1
    Neophyte lbrickpe's Avatar
    Join Date
    Nov 2021
    Location
    Knoxville, TN
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Using Query results in subsequent queries



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Greetings,

    This is the first time I have used Power Query and I am stuck trying to link queries so that later queries use data from a first query as a parameter. Here is my setup:

    I have a web query that retrieves a number of fields from one record in an online database. The record that is selected depends on a user entry in the Excel Sheet behind the query.
    I use the data from some of the retrieved fields directly, passing them back to the spreadsheet. There are three fields that contain ID's or keys, that must be used as parameters in three subsequent queries (to different linked tables) to fetch related data.

    Currently I have the three secondary queries fetching their parameter data from the Excel cells that the first query updated, but this requires manually running the first query, and then the others, or having to hit Refresh All at least twice.

    I'm sure that there is a way to pass data from the first query to the following queries as parameters, but I have not been able to get the right as yet.

    Here is my base query:

    let
    QuNum=Excel.CurrentWorkbook(){[Name="QuoteLookup"]}[Content]{0}[Column1],
    Source = Json.Document(Web.Contents("https://www.teamdesk.net/secure/api/v2/77036/key deleted/Quotation/select.json?column=Quote%20No&column=Customer&column=Contact&column=sales%20engineer&column=description&column=Site%20Name&column=System%20Name&filter=Ends(%5BQuote%20No%5D%2C%20%22" & QuNum & "%22)")),
    Source1 = Source{0}
    in
    Source1

    The other queries are much the same but I need the parameter to get it's value from the table the first query creates.

    I am grateful for any help that comes my way.

    Lawrence
    Lawrence B

  2. #2
    Seeker garylhaas's Avatar
    Join Date
    Apr 2016
    Location
    milwaukee, wi
    Posts
    5
    Articles
    0
    Excel Version
    office 365
    example of how i use custom functions

    1) create a list of text files to process
    2) process each text file using custom function

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="tblFolders"]}[Content],
        FilteredRows_File = Table.SelectRows(Source, each Text.Contains([Folder], "1_gp")),
    
        AddedCustom_fn100_Thrulines = Table.AddColumn(FilteredRows_File, "Custom", each Folder.Files([Folder])),
        ExpandedCustom_fn100_Thrulines = Table.ExpandTableColumn(AddedCustom_fn100_Thrulines, "Custom", 
             {"Name", "Extension", "Date modified"}, {"Name", "Extension", "Date modified"}),
        
        FilteredRows_fn100_Thrulines = Table.SelectRows(ExpandedCustom_fn100_Thrulines, each ([Extension] = ".txt")),
        
        AddedCustom2_fn100_Thrulines = Table.AddColumn(FilteredRows_fn100_Thrulines, "Custom", 
            each fn100_Thrulines_Grandparents([Folder]&""&[Name])),
     
        RemovedErrors_fn100_Thrulines = Table.RemoveRowsWithErrors(AddedCustom2_fn100_Thrulines, {"Custom"}),
        
        ExpandedCustom1_fn100_Thrulines = Table.ExpandTableColumn(RemovedErrors_fn100_Thrulines, "Custom", 
         {"id", "Record", "Line", "Ahnentafel", "nameFirst", "nameSurname", "relation", "Birth", "Death", "Loaded"}, 
         {"id", "Record", "Line", "Ahnentafel", "nameFirst", "nameSurname", "relation", "Birth", "Death", "Loaded"}),
           
        ....
    
    fn100_Thrulines_Grandparents custom function    
    
    
    (zFile)=>
    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents(zFile))}),
        ....
    Last edited by Rebekah; 2022-02-01 at 08:57 PM. Reason: Added code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •