Forum Discussion

DairyG1's avatar
DairyG1
Helper I
4 years ago
Solved

Excel file where comments are linked

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.

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    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!

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      DairyG1
      Helper I
      4 years ago

      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's avatar
        v-kelly-msft
        Community Support
        4 years ago

        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's avatar
    DairyG1
    Helper I
    4 years ago

    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.