Forum Discussion
Analyze in Excel
Hello Community,
I have a Semantic Model in PBI.
I used analyze in excel to integrate columns and measures into a table in Excel.
I transformed the output table in power query in another sheet.
Into this new sheet i added additonal columns where users should input additional data and commentaries.
Is there any way to retain the commentary for each row?
When my model is refreshed and additional rows are added, the values in the input columns changed their place.
The users should only use one table for viewing the data and typing in their information, is possible
7 Replies
yes, it is possible, but I am not entirely sure about your setup.
- You have a table in Excel with data coming from another source
- Add the column "Comments"
- Now select a cell in the Table and do Left-Click|Get Data from Table/Range
- This produces a new query including a column for the comments
- Go to your original query and use a Merge join to add the Comments
You will need something to uniquely identify a row in your table here! - Refresh your Excel table
- It will now have a column "Comments" and a columns "Comments2
- Delete "Comments" and rename "Comments2" to "Comments"
- From now on, each refresh will get the comments entered by the user, add them to your analysis results and use them to repopulate the Comments column, making it appear like the comments were not changed.
I have been using this type of construction in a couple of cases and it is fairly stable and robust.
Only adding and removing columns requires some care...Love your detailed answers PwerQueryKees !
Keep it up!
CristianThanks for your detailed reply, it was really helpful.
Regarding my setup, I have a table which is fed by my semantic model.
After adding the commentary column to this table, I duplicate the Query like you said and merge it by itself via my identifier.
But now both queries have the same source(my semantic model), is this correct?
Wouldnt a change in the rows affect the query which i use for the merge?
Thanks a lot!
My TableHey PBILix ,
You don't have to Duplicate the query: just add the additional data and commentaries to the Transformed table on your secondary sheet and then use it as a "Source" to create the temp table query.
You neet to merge the Transformed table on your secondary sheet (initial transformation query) with this "new" source on some UniqeID and it works.Cristian
See the comprehensive solution about your question in the below link
https://exceleratorbi.com.au/self-referencing-tables-power-query/
Hey PBILix ,
Here is how to do that.
If this answer was helpful, please consider accepting it as the solution to help the other members find it more quickly.
Kudos appreciated also 😉
Cheers,
Cristian Angyal
LinkedIn | X (Twitter) | Romania Power BI User Group | YouTube
