Excel Power Query 2016 custom column for notes

Question

I would like to add a notes column to a merged query table, so that when I refresh the data the notes that I've made on records continue to line up. How can I add a column to do this?

Answer 1

See my answer to this question:

Inserting text manually in a custom column and should be visible on refresh of the report

It includes a link to an explanatory video:

https://youtu.be/duNYHfvP_8U?list=PLmajzIMNl6yH7MvMLmlgGUW5dOsKg74mQ

Answer 2

MySQL provides several variations on INSERT and UPDATE to allow inserting and updating exactly the desired data. These features provide a lot of power and flexibility, making MySQL significantly more capable than it otherwise might be. In this article I’ll give an overview of each feature, help you understand how to choose among them, and point out some things to watch out for.

Setup

I am using MySQL 4.1.15 to create my examples. I assume MyISAM tables without support for transactions, with the following sample data:

create table t1 (
    a int not null primary key,
    b int not null,
    c int not null
) type=MyISAM;

create table t2 (
    d int not null primary key,
    e int not null,
    f int not null
) type=MyISAM;

insert into t1 (a, b, c) values
    (1, 2, 3),
    (2, 4, 6),
    (3, 6, 9);

insert into t2 (d, e, f) values
    (1, 1, 1),
    (4, 4, 4),
    (5, 5, 5);

Overview

Suppose I wish to insert the data from t2 into t1. This data would violate the primary key (a row exists where column a is 1) so the insert will fail: ERROR 1062 (23000): Duplicate entry '1' for key 1. Recall that in MySQL, a primary key is simply a unique index named PRIMARY. Any data that violates any unique index will cause the same problem.

This situation occurs frequently. For example, I might export some data to a spreadsheet, send it to a client, and the client might update or add some data and return the spreadsheet to me. That’s a terrible way to update data, but for various reasons, I’m sure many readers have found themselves in a similar situation. It happens a lot when I’m working with a client who has multiple versions of data in different spreadsheets, and I’m tasked with tidying it all up, standardizing formatting and importing it into a relational database. I have to start with one spreadsheet, then insert and/or update the differences from the others.

What I want to do is either insert only the new rows, or insert the new rows and update the changed rows (depending on the scenario). There are several ways to accomplish both tasks.

Inserting only new rows

If I want to insert only the rows that will not violate the unique index, I can:

Delete duplicate rows from t2 and insert everything that remains:

delete t2 from t2 inner join t1 on a = d;
insert into t1 select * from t2;

The first statement deletes the first row from t2; the second inserts the remaining two. The disadvantage of this approach is that it’s not transactional, since the tables are MyISAM and there are two statements. This may not be an issue if nothing else is altering either table at the same time. Another disadvantage is that I just deleted some data I might want in subsequent queries.