Jun 16 2024 09:18 AM
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?
Jun 16 2024 11:17 AM
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.
Jun 16 2024 11:40 AM
Jun 16 2024 12:30 PM
SolutionThe idea as in attached. But it depends on data logic. How to create the key and how to keep sorting depends on that.
Jun 17 2024 08:19 AM
Than you so much for the help!
Although I have some questions, I went through the solution and didn't understand the purpose of the two excel tables. Could you please elaborate a bit on the solution? I would really appreciate it, you've been a life saver.
Jun 18 2024 01:58 AM
First table is data source. That could be something else - external file, SQL, whatever. Actually we need only second table. Power Query returns data external source into it, that's what we do on first step. After that query returned table again and combine with initial query.
Jun 19 2024 09:53 AM - edited Jun 19 2024 09:54 AM
If the first table ("Source") is the data source, how is it referencing the second table ("Source_1") in its query to load "Source_1".
Can you help me understand the timeline of creation of the two tables. Which table is loaded first and then which query runs on which table?
Sorry if this is a silly question, but I'm a beginner in Power Query and I'm not able to understand.
Jun 21 2024 08:53 AM
Jun 24 2024 05:12 AM
First, you create Source on any data and load result to the grid.
Second, you query that result, that will be Source_1
Third, you open Source again and modify it using Source_1. With that manually added data combined with the data queried from initial source.
Jun 16 2024 12:30 PM
SolutionThe idea as in attached. But it depends on data logic. How to create the key and how to keep sorting depends on that.