Power Query help

[hvl888]

Hi All,

Hoping you could help with my current problem I have with Power Query.

I have a raw data file which is updated monthly. I use Power Query to split this raw data file into a number of workbooks based on the centre code as different members are responsible for their own centre codes. Members would then review their data and insert comments against the relevant row. I then use Append to bring all the files together to include any comments members have for their centre codes. The problem I have is when the raw data is refreshed the following month, often there will be additional entries and when the members update their workbook, the comments that they inserted the previous month no longer match the intended row as new rows are added to their workbook, the comments are now out of sync. Any suggestions you have that would prevent this from happening would be greatly appreciated.

Many Thanks

 

[peter789]

If you are not already using them I think you need to look into self referencing queries. The important point is to establish a unique key to keep the comments with the row it refers to. One way might be to set up an index number in your master data; if this is not possible a unique field or merged fields is an alternative.

Peter

 

[hvl888]

Thank you for your suggestion. I did try using self referencing queries but each time I refreshed the table, it would create a new unwanted column which causes some issues. Although not ideal, i did find a solution to my problem which was to sort the raw data initially and then to add the new data to the end of the data file so when it refreshed, it would be added to the bottom of the table. This way the comments insert previously are still aligned to the relevant row.

 

[peter789]

Regarding the unwanted column. I have found that in most cases if it is deleted it stays deleted even on refresh.