# Power Query with notes in Excel



## Evans2 (May 24, 2016)

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 (May 24, 2016)

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


----------



## Evans2 (May 24, 2016)

Matt Allington said:


> 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 (May 25, 2016)

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 (May 25, 2016)

Evans2 said:


> 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 (May 25, 2016)

Useful said:


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



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 (Jun 8, 2016)

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 (Jun 8, 2016)

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


----------



## Evans2 (Jun 20, 2016)

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 (Jun 21, 2016)

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


----------



## Evans2 (May 24, 2016)

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


----------



## Evans2 (Jun 23, 2016)

Thanks for the link ImKe. Matt's write-up was very useful as well. I think I've got it now. 

My problem was due to a duplicated product ID. Several product samples could be associated with each product ID causing duplicates. I thought I could simply create an Index column but things started to look wonky in my comments column after awhile. 

In my source data, I found that each sample has a unique sample ID that I could pull into Power Query and merge with the product ID to create a new column per your suggestion. No more duplicates! I think everything should work fine now.


----------



## OlaSa (Jul 3, 2016)

I _just_ though a picture might help - follow ImkeF's link in the previous post.
//Ola








By Matt Allington


----------

