Forum Discussion
Manually enter data in rows of excel table using Power Query
I have an excel table (say Table1) which is loaded using a power query.
Table1 has data which looks like the following:
Now I ran a query on table1 to add a blank row for each product, to calculate "Cost of Goods Sold". And I set the default values to be 0 for 2027 and 2028 columns for "Cost of Goods Sold".
Now I want the user to be able to enter the value for "Cost of Goods Sold" in the excel table but every time I refresh the excel, the values that I enter manually get turned back to 0 again.
How do I get the values that I enter to stay?
The idea as in attached. But it depends on data logic. How to create the key and how to keep sorting depends on that.
8 Replies
That's to query table with manually added row and merge it with the query which returns data from external source. For that you need to have (or generate) , unique ID for each records in the table. Merging shall be within the query which load table into the grid.
You may goggle for self-referencing table. Usually that's about manually added columns, but with some modifications shall work for rows.
- Thank you for the help SergeiBaklan
I have tried using the internet and doing some experiments myself to find help but only found solutions for manually added columns as you mentioned. I would really appreciate your help with finding a solution for manually added rows.
Thank you.The idea as in attached. But it depends on data logic. How to create the key and how to keep sorting depends on that.
