SOLVED

Newly (Manually) Inserted Row Disappears when Refreshing the Data on Power Query

Occasional Contributor

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.

15 Replies

@Idreeesi 

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

@L z. 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:

Idreeesi_0-1643653665719.png

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

Idreeesi_2-1643654350584.png

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

Idreeesi_3-1643654500965.png

I hope this is clearer now. 

Thanks.  

@Idreeesi 

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.

 

Hi Sergei
Thank you for your suggestion. I went through it and, regrettably, it doesn't seem to address my requirement. I need the self-referencing to work on 'rows' instead of columns (like in the example I have shared above). I wonder if a solution exists.
best response confirmed by Idreeesi (Occasional Contributor)
Solution

@Idreeesi 

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:

image.png

Thank you Sergei. This worked like a charm!

@Sergei Baklan I have a similar issue that I can't seem to solve. I created the column assigned2 and selected 'JMS' from one of the drop down options for ID 4659. When I refreshed the query it deleted JMS from ID 4659 and added it to line 18. It also rearranged the ID Column.

 

abcrandy_0-1668900277512.png            abcrandy_1-1668900282391.png

 

 

@abcrandy 

Did you use self-referencing technique as in the link above? Data manually added to the table returned by Power Query is not in sync on refresh, you have to sync such data manually.

@Sergei Baklan no its not self-referencing. The "assigned2" field is a drop-down list using values from a different sheet in the same workbook.  When I select an option from the drop down list I would expect the selection to remain for that record after refreshing but it doesn't. The other data comes from sharepoint.

@abcrandy 

That doesn't work such way. Power Query has no idea about manually added data and doesn't care to sync with the. The work around is self-referencing.

@Sergei Baklan Can you provide an example or point me in the right direction