Forum Discussion
Post ProdigyExternal Data Properties
Did you try connecting the Excel table back to powerbi the way I described? This approach works fine within Excel itself.
The main concern with connecting Excel back to PBI is that you would need to immediately refresh the Excell anytime you refreshed the PowerBI. I understand this may not match your work-flow....
I agree that manually copying is error prone and confusing.
Super UserI don't think this can be solved (reliably) this way.
I am even surprised the comment don't get erased altogether upon refresh! Did you place them 'outside' the table???
I do not have alot of experience with PBI, but you could try this:
- Start with your current setup where you load the PBI_Table into your Excel sheet
- Add a column for 'Comment' in the PBI table in excel
- Connect the Excel Table as a data source to your original PBI as Excel_Table
- At the point where you produce the PBI Table, add 2 steps (assuming your last step was called 'PBI_Table':
Table.NestedJoin(PBI_Table, {"Key"}, Excel_Table, {"Key"}, "Excel_Table", JoinKind.LeftOuter)Replace {"Key"} with the list of fields uniquely identifying a row in your table.
You can use the PQ Editor UI for this! - Then
Table.ExpandTableColumn(#"Merged Queries", "Excel_Table", {"Comment"}, {"Comment"})
From now on, whenever you refresh the PBI table, the Comments are fetched from the Excel file and added to the PBI_Table output, overwriting the comments in the Excel table.
I currently do not have PBI on my laptop, so I have not tested all of this. If you are having trouble fixing errors or making it work, feel free to ask ...
Kees Stolker
A big fan of Power Query and Excel
Post ProdigyHello PwerQueryKees
The new column is added in the Excel table itself. When the data refreshes, the column doesn't change, and stays in place, meaning if there's a new row in between or if the data lines switches rows, the Comment do not switch with them.
Connecting Power BI to the Excel file in point #3 would not fix it, as the Excel_Table changes with the PBI_Table. For example, if a comment was entered for Line 3, it will show up for Line 3 at first, but if Line 2 is removed, Line 3 will now be the new Line 2, but the comment stays at Line 3, when it should move to Line 2.
The only workaround I can think of right now is to copy the table in Excel (that originates from PBI dataset), and paste it in a new sheet, meaning it is not linked to any sources and is static, and the new sheet will be merged to the PBI dataset. And whenever an update to the comments is needed, user will refresh the Excel file that originates from PBI dataset, copy the table in Excel again, and overwrite the new sheet.
Although this completes the loop of getting data from PBI to Excel, and pushing it back to PBI, it might be rather confusing for the user.
- PwerQueryKees
Did you try connecting the Excel table back to powerbi the way I described? This approach works fine within Excel itself.
The main concern with connecting Excel back to PBI is that you would need to immediately refresh the Excell anytime you refreshed the PowerBI. I understand this may not match your work-flow....
I agree that manually copying is error prone and confusing.