Forum Discussion
Solved
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 t...
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:
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 spreadsheet
Lorenzo 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.
- Thank you Sergei
I will check this out. It may take me long to implement it as I'm new to Power Query!
Thanks a lot!Idreeesi , you are welcome.
I'd recommend to play first on very simple test file to understand better how it works, after that apply to actual data.