Excel file where comments are linked

[DairyG1]

Hello All,

Hope you may be able to advise.

I have a customer order excel file which updates daily.

I am trying to add comments/updates to order lines and store them.

I need the file to bring in the latest orders whilst keeping the previous comments from past days/weeks.

I have followed the below blog on self referencing tables, but cannot get it to work. I am not sure if the there has been chnages in power query.

Self Referencing Tables in Power Query - Excelerator BI

Thank you for your time.

[DairyG1]

Can anyone advise on a solution for this problem?

Would like to stick with excel as that is the source but open to how to make it manageable in SP list.

Thank you.

[mahoneypat]

Rather than doing what is described in that article, I would encourage you to create a separate Excel sheet (or SharePoint list, etc.) to store your comments along with a key column that you can use to merge into your table that is refreshing.  That way you comments are never lost, and you can update them as needed.

Pat

[DairyG1]

@mahoneypat  ) thank you for responding Pat.

I did try a separate table and merged based on common field and selected all rows from each table but it doesnt store the comments when refreshed.

Steps i took

• I have the source excel file that gets updated daily (connection)
• I created a version of the above with a comments field added
• Then i merged based on common column and outout as a table
• Add comments to the merged table output but when refreshing to pull in latest orders the comments disappear.

I though this would be a common task for people but seem to be little information on how to achieve.

Thank you.

[v-kelly-msft · Accepted solution]

Hi @DairyG1 ,

I agree with what @mahoneypat suggested,you could create another table which saves your comments.

I made a simple example as below:

Suppose your Customer order table is like below:

Then create a Comments table as below where you add your comments:

Then import the 2 tables to power bi,in power query merge the two tables by ID:

Then you will get a result as below:

When you add new comments in Comments table,click Refresh button,you will get an updated result.

Check my sample attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[DairyG1]

@v-kelly-msft  - Kelly thanks so much for doing that example.

I have done all that but it didnt work.

can you please try sorting the data and see if the comments stay with line it was added to?

Can i also ask are you adding the comments to the merged table?

Thank you for the support

[v-kelly-msft]

Hi  @DairyG1 ,

Yes when I sort the column ID descending,the comments stay with the lines which was added to:

The comments are associated with IDs,so no matter whether you are sort the column or not,they will also change accordingly.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!