Jan 31 2022 07:11 AM - edited Jan 31 2022 10:48 AM
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.
Jan 31 2022 09:03 AM
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
Jan 31 2022 10:46 AM
@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:
*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.
Jan 31 2022 11:13 AM
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.
Jan 31 2022 11:30 AM
Jan 31 2022 11:52 AM
@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.
Feb 02 2022 10:44 AM
Feb 02 2022 01:02 PM
SolutionThat'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:
Feb 03 2022 12:05 PM
Feb 04 2022 05:37 AM
@Idreeesi , you are welcome
Nov 19 2022 03:25 PM
@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.
Nov 21 2022 12:13 PM
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.
Nov 21 2022 12:33 PM
@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.
Nov 21 2022 01:45 PM
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.
Nov 21 2022 03:12 PM
@Sergei Baklan Can you provide an example or point me in the right direction
Nov 22 2022 06:11 AM
The link was in above posts, here it is again https://exceleratorbi.com.au/self-referencing-tables-power-query/
Feb 02 2022 01:02 PM
SolutionThat'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: