Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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 though this would be a common task for people but seem to be little information on how to achieve.
Thank you.
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!
@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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.