Inserting text manually in a custom column and should be visible on refresh of the report

Question

We have a table showing data like OpprId, revenue, area region where table is sorted based on highest revenue. This table was built using power query. Here user wants to add a new column and make it as status column and save the status. This status should be visible on refresh of the report connection. So is there any custom column formula to save the entered text, and this status should be in sink with OppId as in future new OppId's may be added so the sort order may change.

OppId   ServiceLineServiceRevenueCUS    Status 
1       101584729.9                     Good
2       62272199                        let u know
3       11000000                        dfghd
4       9000000                         fdgdf
5       8200000                         fdgf 
6       7500000                         fgdf 
7       6000000                         fgdf
8       5650000 

Answer 1

Edit 29/11/2016: a video is now available to clarify this solution. The code is a little bit different from the code below; basically it's still the same solution.

On another forum I answered a similar question. First the input data (first name and last name) was read and output by a Power Query query. A column was added to that output (Age - manually maintained). Now I created a query that reads data from the Input table, left joins it with the Output table and write the results back to the Output table. So the Output table is both input and output from that query.

let
    Source1 = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    Typed1 = Table.TransformColumnTypes(Source1,{{"Last Name", type text}, {"First Name", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Output"]}[Content],
    Typed2 = Table.TransformColumnTypes(Source2,{{"Last Name", type text}, {"First Name", type text}, {"Age" , type number}}),
    Renamed2 = Table.RenameColumns(Typed2,{{"Last Name", "Last"}, {"First Name", "First"}}),
    Join1and2 = Table.Join(Typed1,{"Last Name", "First Name"},Renamed2,{"Last", "First"}, JoinKind.LeftOuter),
    Removed = Table.RemoveColumns(Join1and2,{"Last", "First"})
in
    Removed

Answer 2

If I understand your question correct, you want to generate a table using Power Query, have the user enter info into a cell, and then each time you refresh the user's custom cell doesn't get overwritten?

As long as your user-entered cells aren't part of the columns Power Query loads, this should "just work" today. If you modify cells in the Power Query table, that's going to get overwritten each time you refresh.

---

This isn't the question you asked, but it sounds like you might want to use Microsoft PowerApps to build this report. Power Query isn't going to sync between different versions of the same file...

Disclaimer: I work at Microsoft, in the same business group as PowerApps.

Answer 3

• 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:

1. 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
2. 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
3. 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’
4. 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’
5. In the ‘Workbook Queries’ column, right-click on ‘SQLoutput’ query > ‘Edit’
6. 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
   • 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’
7. 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.
8. 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.