from all my mistakes and trial and error, you cannot add your own columns to a query, they will not be recognized.
there is a round-about way arond it that i use successfully
i will copy it here exactly as i saw it posted. i can no longer find original post to give the site.
i use this sucessfully, its a bit complicated but it works
Marcel Beug did a great job, finding the solution and taking the time to edit a video with an example.
After testing it positively, here is my rehash of his explanations, with more written details in order to hopefully help people not yet very familiar with Excel Power Query.
Situation:
Excel workbook with existing sheet (e.g. “SQLdbOrigin”) containing Original SQL Query data
You wish to add to this original data extra columns with manually entered values, keeping their relationship to the queried data upon refreshes of the original SQL query data
Procedure:
From a cell in the existing original data table (obtained from a DB query for instance), Power query this table to a new output table:
Menu: ‘Data > From Table’ (original data table will be auto named ‘Table1’)
A Power Query Editor window opens, displaying the queried data
In ‘Applied Steps’: Rename ‘Changed Type’ (via F2) to e.g. ‘SQLdata’, and query name to ‘SQLoutput’
Click on menu ‘Close&Load’, which auto creates a new sheet with output table ‘SQLoutput’
One can optionally Rename the sheet and the table
Add (lines outside the table, formats everywhere, and) columns for manual fields attached to ‘SQLoutput’, and fill in data
NB: the definition of the table will auto adjust to include these new columns
From a cell in table ‘SQLoutput’, Power query this output data table:
Menu: ‘Data > From Table’
In ‘Applied Steps’: Rename ‘Changed Type’ (via F2) to e.g. ‘FullData’, and query name to ‘SQLoutput’
Click on ‘Advanced Editor’, which opens a new window for SQLoutput (2)
Copy the 2 lines between ‘let’ and ‘in’
Click on ‘Done’
Close the Power Query Editor window and click ‘Discard’
In the ‘Workbook Queries’ column, right-click on ‘SQLoutput’ query > ‘Edit’
In this Power Query Editor window, modify the query for SQLoutput to merge with its own output table FullData:
Click on ‘Advanced Editor’
Add a comma at the end of the line just before the ‘in’
Paste, just before the line with ‘in’, the 2 lines previously copied (from the query of Table1_2)
Rename ‘Source’ to e.g. ‘Source2’ in the 2 newly added lines (2 times)
Rename the table after ‘in’ from ‘SQLdata’ into ‘FullData’ (as chosen before)
Click on ‘Done’ (and observe the column ‘Query Settings’ on the right)
Click on ‘Merge Queries’
Choose the (only possible) table ‘SQLoutput (Current)’ in the dropdown box
Select the Key column(s) corresponding in the two tables
Select ‘Join Kind’: ‘Left Outer’
Click on ‘OK’
In the Formula Bar of the Power Query Editor (if needed, set checkbox in Menu > View):
Rename the first ‘FullData’ into ‘SQLdata’ (to redefine the two tables to join)
In the header of the new rightmost column, click on the ‘expand’ icon ‘<>’
Deselect the first checkbox ‘(Select All Columns)’
Select in the list the added columns (with the manual values)
Deselect checkbox ‘Use original column name as prefix’
Click ‘OK’
Menu: ‘Close & Load’
Test:
Add and/or delete line(s) in the table of the sheet SQLdbOrigin to simulate a change in the db
Refresh the query (via right click in the table; the ‘Workbook Queries’ on the right can be closed)
Observe that the results are ok
Smile.
Notes:
For calculated cells to retain their functions, add them in the original SQLdbOrigin sheet; Beware: they will behave as expected only for cells within the original table
For calculated cells to work correctly in the SQLoutput table, add first an empty column to the right of the table (which can then be hidden) and add column(s) of calculated fields thereafter. They will not be changed by the queries refresh, and will keep their functions unaltered (for relative addresses, of course)
Do not rename column heads in the original Table1 table in sheet SQLdbOrigin, as they are used in the queries, unless you update all queries accordingly…
Careful also not to change the names of the queries or table, for the same reason…
Formatting of the SQLoutput table seems to hold across refreshes, except for row height always reset.
