Forum Discussion
Newly (Manually) Inserted Row Disappears when Refreshing the Data on Power Query
I have an output sheet of power query that is linked to our bank statements. This report intends to capture all amounts received in our bank account. However, sometimes a customer may pay an amount that pertains to different products, and we need to capture details of amounts received against each product. Therefore, I am tempted to (manually) create rows to allocate the received amount to each product/row.
However, because the output Power Query report is linked to the bank statements, if I manually insert new lines, the newly inserted lines would disappear upon refreshing the data.
Therefore, I wonder if there is a method wherein I could maintain the newly inserted lines without losing the ability to connect to the bank statements upon refreshing the data.
Edit - please find the details on my post below for illustration.
That's practically the same. With above technique add Customer's Name column. Query resulting table once more, append it to main query and remove duplicates on all fields includes Customer's Name.
Very simplified sample:
15 Replies
I might be missing something...but what about:
- Load your bank statements query as Connection only
- Create a separate Table with your manually added records. Also load the corresponding query as Connection only
- Create another query that Append/Combine the above 2 queries then load the output to a spreadsheetLorenzo Thank you for the attempt. However, I believe that my question might not be 100% clear. I will try to make it clearer. Let's take this example:
Let's assume that the details extracted from the bank statements look as follows:
*note: the 'customer's name' on the table above is an additional column added through Power Query (and is not part of the bank statement). The objective of this column is to enable us to capture additional remarks that are not included in the bank statement).
What I would like to do is as follows:
I would like to be able to (manually) insert a row to split the details of Row#2 on the first table into 2 rows (rows# 2 and 3 on the second table above) - in this scenario, the amount shown in row # 2 on the first table is divided into 2 lines (to enable us to capture additional details not available in the bank statements.
After this, I need to be able to refresh the table and extract additional lines (transactions) from the bank statement without losing the manually created lines on the second table above. Example is shown below (the last row needs to be added from the bank statement by refreshing the table):
I hope this is clearer now.
Thanks.
The idea is here Self Referencing Tables in Power Query - Excelerator BI
Technique requires unique ID for the data, you may use as it first 3 columns combined.
