Running SQL Query based on another Query result

Question

I'm using power query to run the following queries. Is there any way to run 2nd Query based on the shipment_nbr result from 1st Query?

Example:

1st Query:

Source = Sql.Database(
    "database.net", "proddb", 
    [
        Query = "SELECT detail.case_date, detail.case_id, detail.shipment_nbr "
              & "FROM view_db.case_detail detail"
    ]
)

2nd Query:

Source = Teradata.Database(
    "edw.prod.com", 
    [
        Query = "SELECT shipment_nbr, invoice "
                & "FROM PROD_DB.billed "
                & "WHERE billed.shipment_nbr in 'XXXXXXXXX' "
    ]
)

Answer 1

Let's say your Query1 returns something like this:

You need to extract the elements from the shipment_nbr column and use them within the query string of Query2

let
    // extract list from column shipment_nbr of Query1
    nbr = Query1[shipment_nbr], 
    
    // create csv-string from list
    csv = Text.Combine( List.Transform(nbr, each "'"&_&"'"), ","),  
    
    // add csv to SQL query
    Source = Teradata.Database(
        "edw.prod.com", 
        [
            Query = "SELECT shipment_nbr, invoice "
                    & "FROM PROD_DB.billed "
                    & "WHERE billed.shipment_nbr in (" & csv & ")"
        ]
    )
in
    Source

In my example the resulting SQL query string itself would look like

SELECT shipment_nbr, invoice 
FROM PROD_DB.billed 
WHERE billed.shipment_nbr in ('xxxxxx','yyyyyy')