Inserting text manually in a custom column and should be visible on refresh of the report
Why Stack Overflow recommends it
Stack Overflow users link it when someone needs manually entered columns in an Excel Power Query table to survive refreshes without getting misaligned.
Recommended by 3 people across 5 different months, 2016 to 2021.
What it is
A short screen-recorded demo by MarcelBeug showing how to keep manually typed comments in an Excel table aligned with SQL data after a Power Query refresh. It uses a self-referencing query that left-joins the input data with the existing output table and writes the result back.
What people said
Recommends MarcelBeug · 2016-11 · Stack Overflow · on “Inserting text manually in a custom column and should be visible on refresh of the report”
Edit 29/11/2016: a video [this 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 Nam
Recommends MarcelBeug · 2017-09 · Stack Overflow · on “Excel Power Query 2016 custom column for notes”
See my answer to this question: Inserting text manually in a custom column and should be visible on refresh of the report [LINK] It includes a link to an explanatory video: [this video]
Recommends Marc Pincince · 2018-06 · Stack Overflow · on “Values hard-coded after power query runs is dislocated when data source updates in Excel”
See @MarcelBeug's answer to this earlier posted question [LINK] . He includes this link to a good video that he made [this video] to show how it's done.
Recommends Marc Pincince · 2018-10 · Stack Overflow · on “Excel Power Query and manual entry”
I think you're trying something similar to what this person was [LINK] . See @MarcelBeug's answer to this earlier posted question [LINK] . He includes this link to a good video [this video] that he made to show how it's done.
Recommends hpb · 2019-09 · Stack Overflow · on “Manual Entered Data On Excel Ms Query Is Misaligned After Refresh”
note that Marcel Beug has given a full solution to this problem in a more recent post in this forum @ Inserting text manually in a custom column and should be visible on refresh of the report [LINK] he has even taken the time to record an example in a video @ [this video]
Cites Jim M. · 2018-08 · Stack Overflow · on “Inserting text manually in Powerquery”
I'm merging multiple Excel files into one where the user can review and mark an additional Comment column as completed. Each day there are additional files and I need to refresh the query and pull the data in. Keeping the original Comment column values. I've attempted to do this by referencing Marcel Beug's video [this video] but that uses an sql table and I cannot seem to get it to work with the Excel files as the source. After the Merge Queries I attempt to modify the first file to my source "InputFile" ![Modify the Merge Formula1][2] ![Changed to last query step of InputFile][3] ![InputFile Query with Source2 and Merge][4] ![M Code of InputFile Query with Merge][5] By setting the First field i
Cites Ario Nugroho Suprapto · 2021-11 · Stack Overflow · on “Adding a formula in a self referencing table power query”
Hi I'm still new to power query and I'm still trying to understand how it fully works. The goal that I'm trying to achieve is to create a table that list my employee data (ID, name and location) from a database and adding several columns to the side for another department to fill in the details. Currently I have made this using a self referencing table. However some of those added columns are using formulas to calculate certain values. I added these formulas in excel, not in power query. The reasoning for this is that the user can see the values update immediately after inputting in the columns. In doing so whenever my query is refreshed, these formulas will get overwritten with values, and
Cites Ario Nugroho Suprapto · 2021-11 · Stack Overflow · on “Adding a formula in a self referencing table power query”
By self referencing I meant something like this [this video] where my first 3 columns are updated with employee data that can shift around or add new ones. The next few columns are inputted manually by a user