SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3092960%22%20slang%3D%22en-US%22%3ENewly%20(Manually)%20Inserted%20Row%20Disappears%20when%20Refreshing%20the%20Data%20on%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092960%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20output%20sheet%20of%20power%20query%20that%20is%20linked%20to%20our%20bank%20statements.%20This%20report%20intends%20to%20capture%20all%20amounts%20received%20in%20our%20bank%20account.%20However%2C%20sometimes%20a%20customer%20may%20pay%20an%20amount%20that%20pertains%20to%20different%20products%2C%20and%20we%20need%20to%20capture%20details%20of%20amounts%20received%20against%20each%20product.%20Therefore%2C%20I%20am%20tempted%20to%20(manually)%20create%20rows%20to%20allocate%20the%20received%20amount%20to%20each%20product%2Frow.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20because%20the%20output%20Power%20Query%20report%20is%20linked%20to%20the%20bank%20statements%2C%20if%20I%20manually%20insert%20new%20lines%2C%20the%20newly%20inserted%20lines%20would%20disappear%20upon%20refreshing%20the%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETherefore%2C%20I%20wonder%20if%20there%20is%20a%20method%20wherein%20I%20could%20maintain%20the%20newly%20inserted%20lines%20without%20losing%20the%20ability%20to%20connect%20to%20the%20bank%20statements%20upon%20refreshing%20the%20data.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3092960%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3093812%22%20slang%3D%22en-US%22%3ERe%3A%20Newly%20(Manually)%20Inserted%20Row%20Disappears%20when%20Refreshing%20the%20Data%20on%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3093812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1143008%22%20target%3D%22_blank%22%3E%40Idreeesi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20might%20be%20missing%20something...but%20what%20about%3A%3CBR%20%2F%3E-%20Load%20your%20bank%20statements%20query%20as%20Connection%20only%3CBR%20%2F%3E-%20Create%20a%20separate%20Table%20with%20your%20manually%20added%20records.%20Also%20load%20the%20corresponding%20query%20as%20Connection%20only%3CBR%20%2F%3E-%20Create%20another%20query%20that%20Append%2FCombine%20the%20above%202%20queries%20then%20load%20the%20output%20to%20a%20spreadsheet%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3094658%22%20slang%3D%22en-US%22%3ERe%3A%20Newly%20(Manually)%20Inserted%20Row%20Disappears%20when%20Refreshing%20the%20Data%20on%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3094658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20attempt.%20However%2C%20I%20believe%20that%20my%20question%20might%20not%20be%20100%25%20clear.%20I%20will%20try%20to%20make%20it%20clearer.%20Let's%20take%20this%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20assume%20that%20the%20details%20extracted%20from%20the%20bank%20statements%20look%20as%20follows%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Idreeesi_0-1643653665719.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343983i75DA531BFF8FC522%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Idreeesi_0-1643653665719.png%22%20alt%3D%22Idreeesi_0-1643653665719.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E*note%3A%20the%20'customer's%20name'%20on%20the%20table%20above%20is%20an%20additional%20column%20added%20through%20Power%20Query%20(and%20is%20not%20part%20of%20the%20bank%20statement).%20The%20objective%20of%20this%20column%20is%20to%20enable%20us%20to%20capture%20additional%20remarks%20that%20are%20not%20included%20in%20the%20bank%20statement).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20do%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Idreeesi_2-1643654350584.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343986i029990023873FD89%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Idreeesi_2-1643654350584.png%22%20alt%3D%22Idreeesi_2-1643654350584.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20(manually)%20insert%20a%20row%20to%20split%20the%20details%20of%20Row%232%20on%20the%20%3CSTRONG%3Efirst%3C%2FSTRONG%3E%20table%20into%202%20rows%20(rows%23%202%20and%203%20on%20the%20second%20table%20above)%20-%20in%20this%20scenario%2C%20the%20amount%20shown%20in%20row%20%23%202%20on%20the%20first%20table%20is%20divided%20into%202%20lines%20(to%20enable%20us%20to%20capture%20additional%20details%20not%20available%20in%20the%20bank%20statements.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20this%2C%20I%20need%20to%20be%20able%20to%20refresh%20the%20table%20and%20extract%20additional%20lines%20(transactions)%20from%20the%20bank%20statement%20without%20losing%20the%20manually%20created%20lines%20on%20the%20second%20table%20above.%20Example%20is%20shown%20below%20(the%20last%20row%20needs%20to%20be%20added%20from%20the%20bank%20statement%20by%20refreshing%20the%20table)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Idreeesi_3-1643654500965.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343988i0E4572ECB38F989E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Idreeesi_3-1643654500965.png%22%20alt%3D%22Idreeesi_3-1643654500965.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20hope%20this%20is%20clearer%20now.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3094956%22%20slang%3D%22en-US%22%3ERe%3A%20Newly%20(Manually)%20Inserted%20Row%20Disappears%20when%20Refreshing%20the%20Data%20on%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3094956%22%20slang%3D%22en-US%22%3EThank%20you%20Sergei%3CBR%20%2F%3EI%20will%20check%20this%20out.%20It%20may%20take%20me%20long%20to%20implement%20it%20as%20I'm%20new%20to%20Power%20Query!%3CBR%20%2F%3EThanks%20a%20lot!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
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.

9 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!