Adding rows in a power query result table without getting them deleted after refresh

[pushap_sood]

On sheet 1 of my workbook, I ran power query to get the data from a excel file. The data is basically name of different projects in a column. I need to add comments under each project (could be multiple comments/action items under each name). The problem is if I add rows in the sheet to add comments/action items, they get deleted once I refresh my query. Any help will be appreciated.
Thanks

 

[Peter Bartholomew]

I suspect that the solution has to somewhat more complicated that you have in mind. Your comments would have to form a separate table with a foreign key to join them to the Power Query output. PQ could then merge your updated comment table back into the output table on refresh.

 

[pushap_sood]

Thanks for your response Peter. What I have done until now:
1. On a second sheet, added project numbers in a column (starting from 1 to say 900)
2. On a third sheet, used XLOOKUP to match the project numbers from my query and second sheet and return project names.
3. Now I have 900 rows on third sheet with the project names in random rows (based on where the project numbers matched).
4. Now I can add rows under any project without deleting any data when I refresh the query.
The problem now I have is: I don't want to bring any rows which don't contain any data. I am going to use this sheet in meetings so have 100s of rows with #N/A or 0 value doesn't look nice. I just want a function which only look for say text values in a column and only return those. The total number of projects are only around 40.

 

[Peter Bartholomew]

Seeing a small, worked example would help. If you want to perform the join with a formula rather than PQ, XLOOKUP should work well. If you build the formula within LET, you could first return your current output and then add a FILTER function at the end to show only the results you want.

 

[GraH - Guido]

It's actually possible by storing the loaded table (the result of the query) in the beginning of the query and buffer it (to keep in memory).
Then you need to construct a logic where to load (append) again the commented lines.
Without seeing some sample data that's difficult to explain (for me at least) how to approach it.

So the query starts with some like =Table.Buffer(Excel.CurrentWorkbook(){[Name="QueryName"]}[Content])
Then you must construct a logic to identify the comments and know to which project they belong too.
Buffer that result too.
Then apply the original query code.
In the end append/merge, sort, whatever ... logic that's required to restore the comments in the correct order under the correct project.
There will be situations where this is not so easy as it looks, and you'd need to adapt your code in a dynamic way to deal with those situations.
It can be considered advanced M.