Excel Power Query技术咨询：用单元格值设INGCODE参数及指定结果输出位置

Great questions! Let's break them down one by one to solve your Power Query needs:

Absolutely, you can pull a cell's value to use as your INGCODE parameter in Power Query. Here's a straightforward way to do it:

• First, for stability, it’s better to define a named range for your target cell (e.g., name the cell INGCODE_Cell instead of using a raw address like Sheet1!A1—this avoids breaks if you add/remove rows/columns later).
• Integrate this into your existing query with this snippet:

let
    // Fetch the cell value as your INGCODE parameter
    INGCODE_Value = Excel.CurrentWorkbook(){[Name="INGCODE_Cell"]}[Content]{0}[Column1],
    // Replace your hardcoded INGCODE value with this variable in your original logic
    OriginalSource = ..., // Your existing query steps go here
    FilteredData = Table.SelectRows(OriginalSource, each [INGCODE] = INGCODE_Value)
in
    FilteredData

• If you prefer using a cell address directly (less recommended but doable), convert the cell to a table first: select the cell, click Insert > Table, then reference that table in your query.

Yes, you have two reliable options depending on your workflow:

Option 1: Use Power Query's built-in load settings (no code required) #

• After finalizing your query, click Close & Load To (not the regular Close & Load button).
• In the pop-up window, select Only Create Connection, then click OK.
• Navigate to your target starting cell (e.g., Sheet2!C3), enter the formula =YourQueryName (replace YourQueryName with your actual query name) and hit Enter. For Excel 365/2021, results will automatically spill to adjacent cells. For older Excel versions:
  • Right-click the query connection in the Queries & Connections pane, select Load To, then specify your target cell range under "Where do you want to put the data?"

Option 2: Use VBA for automated output (great for recurring tasks) #

If you need to automate refresh and output, use this VBA script:

Sub RefreshQueryToSpecificCell()
    Dim targetQuery As WorkbookQuery
    Dim outputStartCell As Range
    
    ' Set your target output cell (adjust sheet and address as needed)
    Set outputStartCell = ThisWorkbook.Sheets("Sheet2").Range("C3")
    
    ' Reference your Power Query by name
    Set targetQuery = ThisWorkbook.Queries("YourQueryName")
    
    ' Refresh the query
    targetQuery.Refresh
    
    ' Clear old content and paste new results
    outputStartCell.CurrentRegion.ClearContents
    ThisWorkbook.Sheets("Sheet1").ListObjects(targetQuery.Name).Range.Copy outputStartCell
End Sub

• Don’t forget to replace YourQueryName, Sheet2, and C3 with your actual query name and target location.

内容的提问来源于stack exchange，提问作者Andrea Gail