Using Query results in subsequent queries

lbrickpe

New member
Joined
Nov 3, 2021
Messages
4
Reaction score
0
Points
0
Age
64
Location
Knoxville, TN
Excel Version(s)
2016
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
 
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 a moderator:
Back
Top