Power Query with notes in Excel

[Evans2]

Hi all,

Finding Power Query a really useful tool. I'm using it to import data from a database into Excel to track certain items. Each row contains data related to a unique item ID.

Once the data is imported to Excel, I would love to be able to create a separate column so that I can create notes. Once the data is updated however, the sort order on the notes column I created is thrown off and is no longer accurate.

Does anyone know a trick I can use to keep this from happening?

Thank you,

Evan

 

[Matt Allington]

I know Imke knows how to do this. I never got around to learning from her, and can't find the link.

 

[Evans2]

I know Imke knows how to do this. I never got around to learning from her, and can't find the link.

I'm trying out an IF formula in a separate column (not included in the PQ table) and placing the notes I want to see in there. It seems to be working so far.

=IF(A:A="P-8025-A","Requested Update","")

=IF(A:A="P-8013-A","Sent sample request 8/23","")

 

[Matt Allington]

Yes, you can always hard code it in the query like this. Ideally however you would be able to get a list in a spreadsheet, you then add your comments, and then it just works. Imke, where are you ?

 

[Useful]

I'm trying out an IF formula in a separate column (not included in the PQ table) and placing the notes I want to see in there. It seems to be working so far.

=IF(A:A="P-8025-A","Requested Update","")

=IF(A:A="P-8013-A","Sent sample request 8/23","")

Hello,
your case is quite normal because you've typed your formula to excel table that will be updated and removed extra columns that not in the query table
You need to add your formula to query editor. Add custom column and insert the below code to get your desired result.
P.S: change [yourcolumnname] as your table column name

if [yourcolumnname]="P-8025-A" then "Requested Update" else
if [yourcolumnname]="P-8013-A" then "Sent sample request 8/23" else ""

 

[Evans2]

Hello,
your case is quite normal because you've typed your formula to excel table that will be updated and removed extra columns that not in the query table
You need to add your formula to query editor. Add custom column and insert the below code to get your desired result.
P.S: change [yourcolumnname] as your table column name

if [yourcolumnname]="P-8025-A" then "Requested Update" else
if [yourcolumnname]="P-8013-A" then "Sent sample request 8/23" else ""

Thanks Matt and Useful. It's an interesting problem. I'd love to eventually find an easier solution to make Power Query even more useful than it already is.

 

[ImkeF]

Hi Evan,
this is indeed a very useful functionality - I'm using it quite often (not only for comments, but also for collecting budget data in tables that also show actuals, previous plans and so on).

The idea is based on the self-referencing-tables I've described here: How to create a Load History or Load Log in Power Query or Power BI – The BIccountant

But I've modified it a bit to make it easier - you can find an example in the file here: https://www.dropbox.com/s/brreiul6d3vhb1g/ReadWriteTable1.xlsx?dl=0

1) Create your import-query and load it to the worksheet
2) Add your column an fill in the comments. Rename this table to "SourceReturn" and load it to Power Query. Now you have 2 queries there: One that imports the external data and another one that shows the result of the last import with the additional columns. You're going to use this new table as a lookup-table for your first import:
3) Go back to your first import-query and merge it with the lookup table "SourceReturn" on the ID. Expand the comment.

 

[Evans2]

So great. I think this is actually working! Thank you ImkeF!!

 

[Evans2]

Hi Imkef,

I noticed that this method is no longer working for one of my worksheets (notes are becoming out of sync with the source data).

I think I'm just doing something wrong.

Here are the steps I'm following:

1) Import query from database to power query and load to worksheet (I had no unique id column so I created an index column). This is Query#1.
2) I add three columns that I want to use to enter my comments in. I then click load from table to Power Query and rename it "SourceReturn"
3) I go back to Query#1 and merge it with my SourceReturn query using the unique index ID and expand the comment columns. Load to workbook.

I've been adding comments to my new writable columns (or so I thought) in Query#1. The comments seemed to stay put at first despite sorting, updating etc. but now some of them are out of sync.

Could you tell me what I could be doing wrong? Could it be because of the index column I created in Power Query?

Thank you again

 

[ImkeF]

Yes, the index is the crucial point. The key for the "self-lookup" needs to sit in the original data itself (can you maybe combine 2 or more columns from your original table to create a unique key). Problem with the index is that it will be allocated differently as your source data changes.

Have a look at the nice write-up Matt just did on this topic: Self Referencing Tables in Power Query - Excelerator BI