External Data Properties

[darentengmfs]

Hello,

I'm connecting a Power BI dataset to an excel file to get a table such that users can go into the excel file and enter comments, which is a new column in the excel file and not a column in Power BI.

The issue is that when a line is removed in the dataset, the lines in excel will move up, except for the newly added "Comments" column, which stays in place, and causing the "Comments" to go to the wrong line.

I tried changing the External Data Properties, but whichever option I choose, it seems to revert back to the first option.

Any help is appreciated. Thank you!

[v-shamiliv]

Hi @darentengmfs 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-shamiliv]

Hi @darentengmfs 
Thank you for reaching out microsoft fabric community forum.
Go to the External Data Properties dialog box in Excel. Under the "Preserve column sort/filter/layout" option,  set to "Clear unused cells" or "Overwrite existing cells with new data" instead of using  "insert entire rows for new data". This should help keep your "Comments" column aligned correctly.
Instead of directly connecting Excel to Power BI, use Power Query to refresh the data. This can give you more control over how the data is updated and aligned.

If you need any further assistance or have any questions, please feel free to reach  us.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community.
Thank you.

[baileymae]

Your solution doesn't work. Please read the question properly. I have the same issue with the OP. The settings don't properly work and always revert back.

[PwerQueryKees]

I 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:

1. Start with your current setup where you load the PBI_Table into your Excel sheet
2. Add a column for 'Comment' in the PBI table in excel
3. Connect the Excel Table as a data source to your original PBI as Excel_Table
4. 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!

5. 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

[darentengmfs]

Hello @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.